database.py

# database.py
# Handles all SQLite database operations for the data plan tracker.
# This module is imported by tracker.py - it should not be run directly.

import sqlite3
import os
from datetime import datetime

# Database file sits in the same folder as this script
DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "plans.db")


def get_connection():
    """Open and return a connection to the SQLite database."""
    conn = sqlite3.connect(DB_PATH)
    # Return rows as dict-like objects so we can access columns by name
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    """
    Create the plans and expenses tables if they do not already exist.
    Safe to call every time the app starts - it will not overwrite existing data.
    """
    conn = get_connection()
    try:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS plans (
                id           INTEGER PRIMARY KEY AUTOINCREMENT,
                name         TEXT    NOT NULL,
                type         TEXT    NOT NULL,
                provider     TEXT,
                assigned_vm  TEXT,
                cost         REAL    DEFAULT 0.0,
                billing_cycle TEXT,
                next_renewal TEXT,
                auto_renew   INTEGER DEFAULT 0,
                notes        TEXT,
                active       INTEGER DEFAULT 1,
                number_expiry TEXT,
                phone_number  TEXT,
                created_at   TEXT
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS expenses (
                id            INTEGER PRIMARY KEY AUTOINCREMENT,
                name          TEXT    NOT NULL,
                category      TEXT,
                cost          REAL    DEFAULT 0.0,
                billing_cycle TEXT,
                notes         TEXT,
                active        INTEGER DEFAULT 1,
                created_at    TEXT
            )
        """)
        conn.commit()
        # Migrations: add columns to existing databases that predate them
        for col_sql in [
            "ALTER TABLE plans ADD COLUMN number_expiry TEXT",
            "ALTER TABLE plans ADD COLUMN phone_number TEXT",
        ]:
            try:
                conn.execute(col_sql)
                conn.commit()
            except Exception:
                pass  # Column already exists
        
        # Create indexes for query performance
        # These improve filtering and sorting operations significantly at scale
        index_statements = [
            ("idx_plans_active", "CREATE INDEX IF NOT EXISTS idx_plans_active ON plans(active)"),
            ("idx_plans_renewal", "CREATE INDEX IF NOT EXISTS idx_plans_renewal ON plans(next_renewal) WHERE next_renewal IS NOT NULL"),
            ("idx_plans_type", "CREATE INDEX IF NOT EXISTS idx_plans_type ON plans(type)"),
            ("idx_plans_vm", "CREATE INDEX IF NOT EXISTS idx_plans_vm ON plans(assigned_vm)"),
            ("idx_plans_provider", "CREATE INDEX IF NOT EXISTS idx_plans_provider ON plans(provider)"),
            ("idx_expenses_active", "CREATE INDEX IF NOT EXISTS idx_expenses_active ON expenses(active)"),
        ]
        for index_name, sql in index_statements:
            try:
                conn.execute(sql)
                conn.commit()
            except Exception:
                pass  # Index already exists or other error
    finally:
        conn.close()


def sanitize_plan_data(data: dict) -> dict:
    """
    Clean and normalize plan data before database insertion.
    - Trims whitespace from text fields
    - Normalizes phone number format
    - Returns a new dict with sanitized values
    """
    sanitized = dict(data)
    text_fields = ['name', 'type', 'provider', 'assigned_vm', 'notes']
    
    for field in text_fields:
        if field in sanitized and sanitized[field] is not None:
            sanitized[field] = str(sanitized[field]).strip()
    
    # Normalize phone number: remove spaces and dashes
    if sanitized.get('phone_number'):
        sanitized['phone_number'] = str(sanitized['phone_number']).replace(' ', '').replace('-', '')
    
    return sanitized


def add_plan(data: dict) -> int:
    """
    Insert a new plan row and return the new row's id.

    data keys must match the column names in the plans table.
    created_at is set automatically if not supplied.
    """
    data = sanitize_plan_data(data)
    data.setdefault("created_at", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    data.setdefault("active", 1)
    data.setdefault("auto_renew", 0)
    data.setdefault("number_expiry", None)
    data.setdefault("phone_number", None)

    conn = get_connection()
    try:
        cursor = conn.execute("""
            INSERT INTO plans
                (name, type, provider, assigned_vm, cost, billing_cycle,
                 next_renewal, auto_renew, notes, active, number_expiry,
                 phone_number, created_at)
            VALUES
                (:name, :type, :provider, :assigned_vm, :cost, :billing_cycle,
                 :next_renewal, :auto_renew, :notes, :active, :number_expiry,
                 :phone_number, :created_at)
        """, data)
        conn.commit()
        return cursor.lastrowid
    finally:
        conn.close()


def get_all_plans(active_only: bool = True) -> list:
    """Return a list of plans. If active_only is True, skip deactivated plans."""
    conn = get_connection()
    try:
        if active_only:
            rows = conn.execute(
                "SELECT * FROM plans WHERE active = 1 ORDER BY type, next_renewal"
            ).fetchall()
        else:
            rows = conn.execute(
                "SELECT * FROM plans ORDER BY type, next_renewal"
            ).fetchall()
        # Convert sqlite3.Row objects to plain dicts for easier use
        return [dict(row) for row in rows]
    finally:
        conn.close()


def get_plan_by_id(plan_id: int) -> dict | None:
    """Return a single plan by its id, or None if not found."""
    conn = get_connection()
    try:
        row = conn.execute(
            "SELECT * FROM plans WHERE id = ?", (plan_id,)
        ).fetchone()
        return dict(row) if row else None
    finally:
        conn.close()


def update_plan(plan_id: int, updates: dict) -> bool:
    """
    Update one or more fields on a plan.
    updates is a dict of {column_name: new_value}.
    Returns True if a row was changed, False if the id was not found.
    """
    if not updates:
        return False

    # Validate and sanitize
    ALLOWED_PLAN_COLUMNS = {
        'name', 'type', 'provider', 'assigned_vm', 'cost',
        'billing_cycle', 'next_renewal', 'auto_renew', 'notes',
        'active', 'number_expiry', 'phone_number'
    }
    invalid = set(updates.keys()) - ALLOWED_PLAN_COLUMNS
    if invalid:
        raise ValueError(f"Invalid columns: {invalid}")
    
    # Sanitize text fields in updates
    updates = sanitize_plan_data(updates)

    # Build "col = ?, col = ?" string dynamically from the keys supplied
    set_clause = ", ".join(f"{col} = ?" for col in updates.keys())
    values = list(updates.values()) + [plan_id]

    conn = get_connection()
    try:
        cursor = conn.execute(
            f"UPDATE plans SET {set_clause} WHERE id = ?", values
        )
        conn.commit()
        return cursor.rowcount > 0
    finally:
        conn.close()


def deactivate_plan(plan_id: int) -> bool:
    """Mark a plan as inactive (soft delete). Returns True on success."""
    return update_plan(plan_id, {"active": 0})


def restore_plan(plan_id: int) -> bool:
    """Reactivate a previously deactivated plan. Returns True on success."""
    return update_plan(plan_id, {"active": 1})


def delete_plan(plan_id: int) -> bool:
    """Permanently delete a plan from the database. Returns True if a row was deleted."""
    conn = get_connection()
    try:
        cursor = conn.execute("DELETE FROM plans WHERE id = ?", (plan_id,))
        conn.commit()
        return cursor.rowcount > 0
    finally:
        conn.close()


# ---------------------------------------------------------------------------
# Expenses (general monthly costs not tied to a plan)
# ---------------------------------------------------------------------------

def get_all_expenses(active_only: bool = True) -> list:
    """Return all expense rows. If active_only, skip deactivated entries."""
    conn = get_connection()
    try:
        if active_only:
            rows = conn.execute(
                "SELECT * FROM expenses WHERE active = 1 ORDER BY category, name"
            ).fetchall()
        else:
            rows = conn.execute(
                "SELECT * FROM expenses ORDER BY category, name"
            ).fetchall()
        return [dict(row) for row in rows]
    finally:
        conn.close()


def add_expense(data: dict) -> int:
    """Insert a new expense and return its id."""
    data.setdefault("created_at", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
    data.setdefault("active", 1)
    conn = get_connection()
    try:
        cursor = conn.execute("""
            INSERT INTO expenses (name, category, cost, billing_cycle, notes, active, created_at)
            VALUES (:name, :category, :cost, :billing_cycle, :notes, :active, :created_at)
        """, data)
        conn.commit()
        return cursor.lastrowid
    finally:
        conn.close()


def update_expense(expense_id: int, updates: dict) -> bool:
    """Update one or more fields on an expense row."""
    if not updates:
        return False
    set_clause = ", ".join(f"{col} = ?" for col in updates.keys())
    values = list(updates.values()) + [expense_id]
    conn = get_connection()
    try:
        cursor = conn.execute(
            f"UPDATE expenses SET {set_clause} WHERE id = ?", values
        )
        conn.commit()
        return cursor.rowcount > 0
    finally:
        conn.close()


def delete_expense(expense_id: int) -> bool:
    """Permanently delete an expense row."""
    conn = get_connection()
    try:
        cursor = conn.execute("DELETE FROM expenses WHERE id = ?", (expense_id,))
        conn.commit()
        return cursor.rowcount > 0
    finally:
        conn.close()


def get_expense_by_id(expense_id: int) -> dict | None:
    """Return a single expense by its id, or None if not found."""
    conn = get_connection()
    try:
        row = conn.execute(
            "SELECT * FROM expenses WHERE id = ?", (expense_id,)
        ).fetchone()
        return dict(row) if row else None
    finally:
        conn.close()


def get_expiring_plans(within_days: int = 14) -> list:
    """
    Return active plans whose next_renewal is within `within_days` days from today.
    Plans with no renewal date are skipped.
    """
    conn = get_connection()
    try:
        today = datetime.now().strftime("%Y-%m-%d")
        rows = conn.execute("""
            SELECT * FROM plans
            WHERE active = 1
              AND next_renewal IS NOT NULL
              AND next_renewal != ''
              AND next_renewal <= date(?, '+' || ? || ' days')
              AND next_renewal >= ?
            ORDER BY next_renewal
        """, (today, within_days, today)).fetchall()
        return [dict(row) for row in rows]
    finally:
        conn.close()


# ---------------------------------------------------------------------------
# Audit Logging
# ---------------------------------------------------------------------------

def init_audit_table():
    """Create the audit_log table if it doesn't exist."""
    conn = get_connection()
    try:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS audit_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT NOT NULL,
                action TEXT NOT NULL,
                plan_id INTEGER,
                expense_id INTEGER,
                details TEXT,
                user TEXT
            )
        """)
        conn.commit()
    finally:
        conn.close()


def log_audit(action: str, plan_id: int = None, expense_id: int = None, details: str = "", user: str = ""):
    """Log an action to the audit log."""
    init_audit_table()
    conn = get_connection()
    try:
        conn.execute("""
            INSERT INTO audit_log (timestamp, action, plan_id, expense_id, details, user)
            VALUES (?, ?, ?, ?, ?, ?)
        """, (datetime.now().strftime("%Y-%m-%d %H:%M:%S"), action, plan_id, expense_id, details, user))
        conn.commit()
    finally:
        conn.close()


def get_audit_logs(limit: int = 100, plan_id: int = None, action: str = None) -> list:
    """Get recent audit logs, optionally filtered by plan_id or action."""
    init_audit_table()
    conn = get_connection()
    try:
        query = "SELECT * FROM audit_log WHERE 1=1"
        params = []
        
        if plan_id:
            query += " AND plan_id = ?"
            params.append(plan_id)
        if action:
            query += " AND action = ?"
            params.append(action)
        
        query += " ORDER BY timestamp DESC LIMIT ?"
        params.append(limit)
        
        rows = conn.execute(query, params).fetchall()
        return [dict(row) for row in rows]
    finally:
        conn.close()


# ---------------------------------------------------------------------------
# Alert History Tracking
# ---------------------------------------------------------------------------

def init_alert_log_table():
    """Create the alert_log table if it doesn't exist."""
    conn = get_connection()
    try:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS alert_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TEXT NOT NULL,
                plan_id INTEGER,
                alert_type TEXT NOT NULL,
                channels TEXT,
                message_summary TEXT,
                delivered INTEGER DEFAULT 0
            )
        """)
        conn.commit()
    finally:
        conn.close()


def log_alert(alert_type: str, plan_id: int = None, channels: list = None, 
              message_summary: str = "", delivered: bool = True):
    """Log an alert to the alert history."""
    init_alert_log_table()
    conn = get_connection()
    try:
        conn.execute("""
            INSERT INTO alert_log (timestamp, plan_id, alert_type, channels, message_summary, delivered)
            VALUES (?, ?, ?, ?, ?, ?)
        """, (
            datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
            plan_id,
            alert_type,
            ",".join(channels) if channels else "",
            message_summary[:200],  # Truncate to reasonable length
            1 if delivered else 0
        ))
        conn.commit()
    finally:
        conn.close()


def get_alert_logs(limit: int = 100, plan_id: int = None, alert_type: str = None) -> list:
    """Get recent alert logs, optionally filtered by plan_id or alert_type."""
    init_alert_log_table()
    conn = get_connection()
    try:
        query = "SELECT * FROM alert_log WHERE 1=1"
        params = []
        
        if plan_id:
            query += " AND plan_id = ?"
            params.append(plan_id)
        if alert_type:
            query += " AND alert_type = ?"
            params.append(alert_type)
        
        query += " ORDER BY timestamp DESC LIMIT ?"
        params.append(limit)
        
        rows = conn.execute(query, params).fetchall()
        return [dict(row) for row in rows]
    finally:
        conn.close()


# ---------------------------------------------------------------------------
# Plan History Tracking
# ---------------------------------------------------------------------------

def init_plan_history_table():
    """Create the plan_history table for tracking cost changes."""
    conn = get_connection()
    try:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS plan_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                plan_id INTEGER NOT NULL,
                timestamp TEXT NOT NULL,
                field_changed TEXT NOT NULL,
                old_value TEXT,
                new_value TEXT
            )
        """)
        conn.commit()
    finally:
        conn.close()


def log_plan_change(plan_id: int, field: str, old_value: str, new_value: str):
    """Log a change to a plan's field (cost, billing_cycle, etc.)."""
    init_plan_history_table()
    conn = get_connection()
    try:
        conn.execute("""
            INSERT INTO plan_history (plan_id, timestamp, field_changed, old_value, new_value)
            VALUES (?, ?, ?, ?, ?)
        """, (plan_id, datetime.now().strftime("%Y-%m-%d %H:%M:%S"), field, old_value, new_value))
        conn.commit()
    finally:
        conn.close()


def get_plan_history(plan_id: int, limit: int = 50) -> list:
    """Get change history for a specific plan."""
    init_plan_history_table()
    conn = get_connection()
    try:
        rows = conn.execute("""
            SELECT * FROM plan_history 
            WHERE plan_id = ? 
            ORDER BY timestamp DESC 
            LIMIT ?
        """, (plan_id, limit)).fetchall()
        return [dict(row) for row in rows]
    finally:
        conn.close()


def get_cost_changes(plan_id: int = None) -> list:
    """Get cost change history, optionally filtered by plan."""
    init_plan_history_table()
    conn = get_connection()
    try:
        query = "SELECT * FROM plan_history WHERE field_changed = 'cost'"
        params = []
        
        if plan_id:
            query += " AND plan_id = ?"
            params.append(plan_id)
        
        query += " ORDER BY timestamp DESC"
        
        rows = conn.execute(query, params).fetchall()
        return [dict(row) for row in rows]
    finally:
        conn.close()


def advance_auto_renew_plans(today_str: str = None) -> int:
    """
    Advance next_renewal for auto_renew plans whose date is today or past.
    Returns the number of plans updated.
    """
    try:
        from dateutil.relativedelta import relativedelta
    except ImportError:
        return 0  # dateutil not available; skip auto-advance

    from datetime import datetime, timedelta

    if today_str is None:
        today = datetime.now().date()
    else:
        today = datetime.strptime(today_str, "%Y-%m-%d").date()

    plans = get_all_plans(active_only=True)
    updated = 0

    for plan in plans:
        if not plan.get("auto_renew"):
            continue
        renewal_str = plan.get("next_renewal")
        if not renewal_str:
            continue
        try:
            renewal = datetime.strptime(renewal_str, "%Y-%m-%d").date()
        except ValueError:
            continue

        if renewal > today:
            continue  # not due yet

        cycle = plan.get("billing_cycle", "monthly")
        current = renewal

        if cycle == "monthly":
            while current <= today:
                current += relativedelta(months=+1)
        elif cycle == "28-day":
            while current <= today:
                current += timedelta(days=28)
        elif cycle == "annual":
            while current <= today:
                current += relativedelta(months=+12)
        elif cycle == "one-off":
            continue
        else:
            # default to monthly for unknown cycles
            while current <= today:
                current += relativedelta(months=+1)

        new_str = current.strftime("%Y-%m-%d")
        if new_str != renewal_str:
            update_plan(plan["id"], {"next_renewal": new_str})
            log_audit(
                "auto_advance",
                plan_id=plan["id"],
                details=f"Auto-renewed from {renewal_str} to {new_str} ({cycle})"
            )
            updated += 1

    return updated