import sqlite3
import datetime

def init_db():
    conn = sqlite3.connect('crm.db')
    c = conn.cursor()

    c.execute('''CREATE TABLE IF NOT EXISTS contacts (
        id INTEGER PRIMARY KEY,
        name TEXT,
        email TEXT UNIQUE,
        phone TEXT,
        company TEXT,
        status TEXT DEFAULT 'lead',
        created_at TEXT,
        last_contact TEXT,
        notes TEXT
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS deals (
        id INTEGER PRIMARY KEY,
        contact_id INTEGER,
        title TEXT,
        value REAL,
        stage TEXT DEFAULT 'prospect',
        expected_close TEXT,
        notes TEXT,
        FOREIGN KEY (contact_id) REFERENCES contacts (id)
    )''')

    c.execute('''CREATE TABLE IF NOT EXISTS interactions (
        id INTEGER PRIMARY KEY,
        contact_id INTEGER,
        type TEXT,
        notes TEXT,
        created_at TEXT,
        FOREIGN KEY (contact_id) REFERENCES contacts (id)
    )''')

    conn.commit()
    return conn

def add_contact(conn, name, email, company=None, phone=None, status='lead'):
    c = conn.cursor()
    now = datetime.datetime.now().isoformat()
    c.execute('''INSERT OR REPLACE INTO contacts
                 (name, email, phone, company, status, created_at, last_contact)
                 VALUES (?, ?, ?, ?, ?, ?, ?)''',
              (name, email, phone, company, status, now, now))
    conn.commit()
    return c.lastrowid

def update_contact_status(conn, email, new_status):
    """Move contact through pipeline: lead -> qualified -> proposal -> won/lost"""
    valid_statuses = ['lead', 'qualified', 'proposal', 'negotiation', 'won', 'lost']
    if new_status not in valid_statuses:
        raise ValueError(f"Invalid status. Choose from: {valid_statuses}")

    c = conn.cursor()
    c.execute('UPDATE contacts SET status = ?, last_contact = ? WHERE email = ?',
              (new_status, datetime.datetime.now().isoformat(), email))
    conn.commit()

import smtplib
from email.mime.text import MIMEText

def check_follow_ups(conn, days_threshold=7):
    """Find contacts not touched in X days."""
    c = conn.cursor()
    cutoff = (datetime.datetime.now() - datetime.timedelta(days=days_threshold)).isoformat()

    c.execute('''SELECT name, email, company, status, last_contact
                 FROM contacts
                 WHERE last_contact < ? AND status NOT IN ('won', 'lost')
                 ORDER BY last_contact ASC''', (cutoff,))

    return c.fetchall()

def send_follow_up_digest(overdue_contacts, your_email, smtp_password):
    """Email yourself a daily digest of overdue follow-ups."""
    if not overdue_contacts:
        return

    body = "Daily CRM Follow-Up Digest\n\n"
    body += f"Contacts needing attention ({len(overdue_contacts)}):\n\n"

    for contact in overdue_contacts:
        name, email, company, status, last_contact = contact
        days_ago = (datetime.datetime.now() - datetime.datetime.fromisoformat(last_contact)).days
        body += f"- {name} ({company or 'No company'}): {status} pipeline, last contacted {days_ago} days ago\n"
        body += f"  Email: {email}\n\n"

    msg = MIMEText(body)
    msg['Subject'] = f"CRM Alert: {len(overdue_contacts)} contacts need follow-up"
    msg['From'] = your_email
    msg['To'] = your_email

    with smtplib.SMTP_SSL('smtp.gmail.com', 465) as server:
        server.login(your_email, smtp_password)
        server.send_message(msg)

def add_deal(conn, contact_email, title, value, expected_close_days=30):
    c = conn.cursor()
    contact = c.execute('SELECT id FROM contacts WHERE email = ?', (contact_email,)).fetchone()
    if not contact:
        raise ValueError(f"Contact not found: {contact_email}")

    expected_close = (datetime.datetime.now() + datetime.timedelta(days=expected_close_days)).date().isoformat()

    c.execute('''INSERT INTO deals (contact_id, title, value, stage, expected_close)
                 VALUES (?, ?, ?, 'prospect', ?)''',
              (contact[0], title, value, expected_close))
    conn.commit()
    return c.lastrowid

def get_pipeline_summary(conn):
    """Get pipeline value by stage."""
    c = conn.cursor()
    c.execute('''SELECT stage, COUNT(*), SUM(value)
                 FROM deals
                 WHERE stage NOT IN ('won', 'lost')
                 GROUP BY stage''')

    results = c.fetchall()
    print("\n=== Pipeline Summary ===")
    total_value = 0
    for stage, count, value in results:
        print(f"{stage.capitalize()}: {count} deals worth ${value:,.0f}")
        total_value += value
    print(f"Total Pipeline: ${total_value:,.0f}")
    return results

def run_daily_crm():
    conn = init_db()

    # Check for overdue follow-ups
    overdue = check_follow_ups(conn, days_threshold=5)

    if overdue:
        send_follow_up_digest(overdue, "you@gmail.com", "your-app-password")
        print(f"WARNING: {len(overdue)} contacts need follow-up")
    else:
        print("All contacts up to date")

    # Show pipeline
    get_pipeline_summary(conn)
    conn.close()

if __name__ == '__main__':
    run_daily_crm()

0 9 * * 1-5 /usr/bin/python3 /path/to/crm.py >> /var/log/crm.log 2>&1