database.py

"""
database.py — SQLite CRM Schema + All DB Operations
Free Tier: SQLite (100% free, no signup needed)
"""

import sqlite3
import os
from datetime import datetime

DB_PATH = os.path.join(os.path.dirname(__file__), "crm.db")


def get_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row  # lets you access columns by name
    return conn


def init_db():
    """Create all tables if they don't exist."""
    conn = get_connection()
    c = conn.cursor()

    # ── LEADS TABLE ──────────────────────────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS leads (
        id              INTEGER PRIMARY KEY AUTOINCREMENT,
        business_name   TEXT NOT NULL,
        owner_name      TEXT,
        business_type   TEXT,          -- restaurant / startup / agency / creator / shop
        city            TEXT,
        website         TEXT,
        whatsapp        TEXT,
        email           TEXT,
        instagram       TEXT,
        linkedin        TEXT,
        google_reviews  INTEGER DEFAULT 0,
        ig_followers    INTEGER DEFAULT 0,
        score           INTEGER DEFAULT 0,
        status          TEXT DEFAULT 'new',   -- new / contacted / replied / qualified / closed
        preferred_channel TEXT,
        notes           TEXT,
        created_at      TEXT DEFAULT (datetime('now')),
        updated_at      TEXT DEFAULT (datetime('now'))
    )
    """)

    # ── MESSAGES TABLE ────────────────────────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS messages (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        lead_id     INTEGER NOT NULL,
        channel     TEXT NOT NULL,     -- whatsapp / email / instagram / linkedin
        direction   TEXT NOT NULL,     -- outbound / inbound
        content     TEXT NOT NULL,
        status      TEXT DEFAULT 'draft',  -- draft / sent / delivered / read / replied
        sent_at     TEXT,
        created_at  TEXT DEFAULT (datetime('now')),
        FOREIGN KEY (lead_id) REFERENCES leads(id)
    )
    """)

    # ── FOLLOW-UPS TABLE ──────────────────────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS followups (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        lead_id     INTEGER NOT NULL,
        scheduled   TEXT NOT NULL,     -- datetime string
        channel     TEXT NOT NULL,
        message     TEXT NOT NULL,
        done        INTEGER DEFAULT 0, -- 0=pending, 1=done
        FOREIGN KEY (lead_id) REFERENCES leads(id)
    )
    """)

    # ── SCORES TABLE ─────────────────────────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS scores (
        id              INTEGER PRIMARY KEY AUTOINCREMENT,
        lead_id         INTEGER NOT NULL UNIQUE,
        website_score   INTEGER DEFAULT 0,
        social_score    INTEGER DEFAULT 0,
        channel_score   INTEGER DEFAULT 0,
        potential_score INTEGER DEFAULT 0,
        total           INTEGER DEFAULT 0,
        updated_at      TEXT DEFAULT (datetime('now')),
        FOREIGN KEY (lead_id) REFERENCES leads(id)
    )
    """)

    # ── AGENT LOGS TABLE ──────────────────────────────────────────
    c.execute("""
    CREATE TABLE IF NOT EXISTS agent_logs (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        timestamp   TEXT DEFAULT (datetime('now')),
        level       TEXT DEFAULT 'info',  -- info / success / warning / error
        message     TEXT NOT NULL
    )
    """)

    conn.commit()
    conn.close()
    print("Database ready -> crm.db")


# ── LEAD CRUD ─────────────────────────────────────────────────────

def add_lead(data: dict) -> int:
    conn = get_connection()
    c = conn.cursor()
    c.execute("""
        INSERT INTO leads
            (business_name, owner_name, business_type, city,
             website, whatsapp, email, instagram, linkedin,
             google_reviews, ig_followers, notes)
        VALUES
            (:business_name, :owner_name, :business_type, :city,
             :website, :whatsapp, :email, :instagram, :linkedin,
             :google_reviews, :ig_followers, :notes)
    """, data)
    lead_id = c.lastrowid
    conn.commit()
    conn.close()
    return lead_id


def get_lead(lead_id: int) -> dict:
    conn = get_connection()
    c = conn.cursor()
    row = c.execute("SELECT * FROM leads WHERE id=?", (lead_id,)).fetchone()
    conn.close()
    return dict(row) if row else None


def get_all_leads(status: str = None) -> list:
    conn = get_connection()
    c = conn.cursor()
    if status:
        rows = c.execute("SELECT * FROM leads WHERE status=? ORDER BY score DESC", (status,)).fetchall()
    else:
        rows = c.execute("SELECT * FROM leads ORDER BY score DESC").fetchall()
    conn.close()
    return [dict(r) for r in rows]


def update_lead(lead_id: int, fields: dict):
    fields["updated_at"] = datetime.now().isoformat()
    fields["id"] = lead_id
    set_clause = ", ".join(f"{k}=:{k}" for k in fields if k != "id")
    conn = get_connection()
    conn.execute(f"UPDATE leads SET {set_clause} WHERE id=:id", fields)
    conn.commit()
    conn.close()


def delete_lead(lead_id: int):
    conn = get_connection()
    conn.execute("DELETE FROM leads WHERE id=?", (lead_id,))
    conn.execute("DELETE FROM messages WHERE lead_id=?", (lead_id,))
    conn.execute("DELETE FROM followups WHERE lead_id=?", (lead_id,))
    conn.execute("DELETE FROM scores WHERE lead_id=?", (lead_id,))
    conn.commit()
    conn.close()


# ── MESSAGE CRUD ──────────────────────────────────────────────────

def save_message(lead_id: int, channel: str, direction: str, content: str, status="draft") -> int:
    conn = get_connection()
    c = conn.cursor()
    c.execute("""
        INSERT INTO messages (lead_id, channel, direction, content, status)
        VALUES (?, ?, ?, ?, ?)
    """, (lead_id, channel, direction, content, status))
    msg_id = c.lastrowid
    conn.commit()
    conn.close()
    return msg_id


def mark_message_sent(msg_id: int):
    conn = get_connection()
    conn.execute(
        "UPDATE messages SET status='sent', sent_at=datetime('now') WHERE id=?",
        (msg_id,)
    )
    conn.commit()
    conn.close()


def get_messages(lead_id: int) -> list:
    conn = get_connection()
    rows = conn.execute(
        "SELECT * FROM messages WHERE lead_id=? ORDER BY created_at", (lead_id,)
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


# ── SCORE UPSERT ──────────────────────────────────────────────────

def save_score(lead_id: int, breakdown: dict):
    conn = get_connection()
    conn.execute("""
        INSERT INTO scores (lead_id, website_score, social_score, channel_score, potential_score, total)
        VALUES (:lead_id, :website_score, :social_score, :channel_score, :potential_score, :total)
        ON CONFLICT(lead_id) DO UPDATE SET
            website_score=excluded.website_score,
            social_score=excluded.social_score,
            channel_score=excluded.channel_score,
            potential_score=excluded.potential_score,
            total=excluded.total,
            updated_at=datetime('now')
    """, {"lead_id": lead_id, **breakdown})
    conn.execute("UPDATE leads SET score=? WHERE id=?", (breakdown["total"], lead_id))
    conn.commit()
    conn.close()


# ── FOLLOWUP CRUD ─────────────────────────────────────────────────

def schedule_followup(lead_id: int, scheduled: str, channel: str, message: str):
    conn = get_connection()
    conn.execute("""
        INSERT INTO followups (lead_id, scheduled, channel, message)
        VALUES (?, ?, ?, ?)
    """, (lead_id, scheduled, channel, message))
    conn.commit()
    conn.close()


def get_pending_followups() -> list:
    conn = get_connection()
    now = datetime.now().isoformat()
    rows = conn.execute("""
        SELECT f.*, l.business_name FROM followups f
        JOIN leads l ON f.lead_id = l.id
        WHERE f.done=0 AND f.scheduled <= ?
        ORDER BY f.scheduled
    """, (now,)).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def mark_followup_done(followup_id: int):
    conn = get_connection()
    conn.execute("UPDATE followups SET done=1 WHERE id=?", (followup_id,))
    conn.commit()
    conn.close()


# ── AGENT LOGS CRUD ───────────────────────────────────────────────

def log_agent_activity(message: str, level: str = "info"):
    """Log an activity message from the autonomous agent to the database."""
    conn = get_connection()
    conn.execute(
        "INSERT INTO agent_logs (message, level) VALUES (?, ?)",
        (message, level)
    )
    conn.commit()
    conn.close()


def get_agent_logs(limit: int = 100) -> list:
    """Retrieve the latest autonomous agent logs."""
    conn = get_connection()
    rows = conn.execute(
        "SELECT id, timestamp, level, message FROM agent_logs ORDER BY id DESC LIMIT ?",
        (limit,)
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def clear_agent_logs():
    """Clear all agent logs from the database."""
    conn = get_connection()
    conn.execute("DELETE FROM agent_logs")
    conn.commit()
    conn.close()


def get_score_breakdown(lead_id: int) -> dict:
    """Retrieve the score breakdown for a lead."""
    conn = get_connection()
    c = conn.cursor()
    row = c.execute("SELECT * FROM scores WHERE lead_id=?", (lead_id,)).fetchone()
    conn.close()
    if row:
        return dict(row)
    else:
        return {
            "website_score": 0,
            "social_score": 0,
            "channel_score": 0,
            "potential_score": 0,
            "total": 0
        }