data/database.py

"""
data/database.py  —  SQLite backend for FixItGo
Enhanced with:
  - time_slots table (worker availability)
  - Double-booking prevention (atomic slot reservation)
  - Booking status flow: pending → accepted → completed / cancelled
  - Review duplicate prevention
  - Top-rated worker query
"""
import sqlite3
import hashlib
import os

DB = os.path.join(os.path.dirname(__file__), "..", "fixitgo.db")


def _conn():
    c = sqlite3.connect(DB, timeout=30, check_same_thread=False)
    c.row_factory = sqlite3.Row
    c.execute("PRAGMA foreign_keys = ON")
    c.execute("PRAGMA journal_mode = WAL")   # better concurrency
    c.execute("PRAGMA busy_timeout = 5000")
    return c


def _h(pw: str) -> str:
    return hashlib.sha256(pw.encode()).hexdigest()


# ── Create / Migrate Tables ───────────────────────────────────────────────────
def init_db():
    c = _conn()
    c.executescript("""
    CREATE TABLE IF NOT EXISTS users (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        name       TEXT    NOT NULL DEFAULT '',
        phone      TEXT    NOT NULL UNIQUE,
        password   TEXT    NOT NULL,
        city       TEXT    DEFAULT 'Chennai',
        area       TEXT    DEFAULT '',
        created_at TEXT    DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS workers (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        name          TEXT    NOT NULL,
        phone         TEXT    NOT NULL,
        category      TEXT    NOT NULL,
        skills        TEXT    DEFAULT '',
        bio           TEXT    DEFAULT '',
        area          TEXT    NOT NULL,
        city          TEXT    DEFAULT 'Chennai',
        base_price    INTEGER DEFAULT 300,
        rating        REAL    DEFAULT 0,
        total_reviews INTEGER DEFAULT 0,
        total_jobs    INTEGER DEFAULT 0,
        is_available  INTEGER DEFAULT 1,
        is_approved   INTEGER DEFAULT 1,
        photo_emoji   TEXT    DEFAULT '👷'
    );

    CREATE TABLE IF NOT EXISTS time_slots (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        worker_id   INTEGER NOT NULL REFERENCES workers(id) ON DELETE CASCADE,
        slot_date   TEXT    NOT NULL,   -- ISO date: '2026-03-20'
        slot_time   TEXT    NOT NULL,   -- e.g.  '10:00 AM'
        is_booked   INTEGER DEFAULT 0,  -- 0 = free, 1 = taken
        UNIQUE(worker_id, slot_date, slot_time)
    );

    CREATE TABLE IF NOT EXISTS bookings (
        id             INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id        INTEGER REFERENCES users(id),
        worker_id      INTEGER REFERENCES workers(id),
        slot_id        INTEGER REFERENCES time_slots(id),
        category       TEXT    NOT NULL,
        issue          TEXT    NOT NULL,
        address        TEXT    NOT NULL,
        scheduled_at   TEXT    DEFAULT '',
        status         TEXT    DEFAULT 'pending',
        payment_method TEXT    DEFAULT 'upi',
        visit_charge   INTEGER DEFAULT 0,
        platform_fee   INTEGER DEFAULT 20,
        total          INTEGER DEFAULT 0,
        created_at     TEXT    DEFAULT (datetime('now'))
    );

    CREATE TABLE IF NOT EXISTS reviews (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        booking_id  INTEGER UNIQUE,              -- one review per booking
        user_id     INTEGER REFERENCES users(id),
        worker_id   INTEGER REFERENCES workers(id),
        rating      INTEGER NOT NULL CHECK(rating BETWEEN 1 AND 5),
        comment     TEXT    DEFAULT '',
        reviewer    TEXT    DEFAULT '',
        created_at  TEXT    DEFAULT (datetime('now'))
    );
    """)
    c.commit()
    c.close()


# ── Auth ──────────────────────────────────────────────────────────────────────
def register_user(name, phone, password, city="Chennai", area=""):
    try:
        with sqlite3.connect(DB, timeout=30) as c:
            c.row_factory = sqlite3.Row
            c.execute(
                "INSERT INTO users (name,phone,password,city,area) VALUES (?,?,?,?,?)",
                (name.strip(), phone.strip(), _h(password), city.strip(), area.strip())
            )
            u = dict(c.execute(
                "SELECT * FROM users WHERE phone=?", (phone.strip(),)
            ).fetchone())
        return u, None
    except sqlite3.IntegrityError:
        return None, "Phone number already registered. Please login."


def login_user(phone, password):
    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        row = c.execute(
            "SELECT * FROM users WHERE phone=? AND password=?",
            (phone.strip(), _h(password))
        ).fetchone()
    return (dict(row), None) if row else (None, "Wrong phone or password.")


# ── Workers ───────────────────────────────────────────────────────────────────
def get_workers(category=None, area=None, sort="rating"):
    q = "SELECT * FROM workers WHERE is_approved=1"
    p = []
    if category and category != "All":
        q += " AND category=?";  p.append(category)
    if area and area.strip():
        q += " AND (area LIKE ? OR city LIKE ?)"; p += [f"%{area}%", f"%{area}%"]
    order = {
        "rating": "rating DESC, total_reviews DESC",
        "price":  "base_price ASC",
        "jobs":   "total_jobs DESC",
    }.get(sort, "rating DESC")
    q += f" ORDER BY {order}"
    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        rows = c.execute(q, p).fetchall()
    return [dict(r) for r in rows]


def get_worker(wid):
    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        row = c.execute("SELECT * FROM workers WHERE id=?", (wid,)).fetchone()
    return dict(row) if row else None


def get_top_rated_workers(category=None, limit=3):
    """Return top-rated (rating≥4) workers, optionally filtered by category."""
    q = "SELECT * FROM workers WHERE is_approved=1 AND rating >= 4.0"
    p = []
    if category and category != "All":
        q += " AND category=?"; p.append(category)
    q += " ORDER BY rating DESC, total_reviews DESC LIMIT ?"
    p.append(limit)
    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        rows = c.execute(q, p).fetchall()
    return [dict(r) for r in rows]


def get_reviews(worker_id, limit=5):
    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        rows = c.execute(
            "SELECT * FROM reviews WHERE worker_id=? ORDER BY created_at DESC LIMIT ?",
            (worker_id, limit)
        ).fetchall()
    return [dict(r) for r in rows]


# ── Time Slots ────────────────────────────────────────────────────────────────
def get_worker_slots(worker_id: int, slot_date: str) -> list[dict]:
    """Return all time slots for a worker on a given date."""
    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        rows = c.execute(
            "SELECT * FROM time_slots WHERE worker_id=? AND slot_date=? ORDER BY id",
            (worker_id, slot_date)
        ).fetchall()
    return [dict(r) for r in rows]


def get_available_slots(worker_id: int, slot_date: str) -> list[dict]:
    """Return only free (not booked) slots for a worker on a date."""
    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        rows = c.execute(
            "SELECT * FROM time_slots WHERE worker_id=? AND slot_date=? AND is_booked=0 ORDER BY id",
            (worker_id, slot_date)
        ).fetchall()
    return [dict(r) for r in rows]


def seed_slots_for_worker(worker_id: int, dates: list[str], times: list[str]):
    """
    Insert time slots for a worker. Skips duplicates (UNIQUE constraint).
    Call from data/seed.py.
    """
    with sqlite3.connect(DB, timeout=30) as c:
        for d in dates:
            for t in times:
                c.execute(
                    "INSERT OR IGNORE INTO time_slots (worker_id, slot_date, slot_time) VALUES (?,?,?)",
                    (worker_id, d, t)
                )


# ── Bookings ──────────────────────────────────────────────────────────────────
def create_booking(user_id, worker_id, category, issue,
                   address, scheduled_at, payment_method, visit_charge,
                   slot_id=None):
    """
    Create a booking. If slot_id is provided, atomically marks the slot as
    booked — raises ValueError if slot is already taken (double-booking guard).
    """
    fee   = 20
    total = int(visit_charge) + fee

    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        c.execute("PRAGMA foreign_keys = ON")

        # ── Double-booking check ──────────────────────────────────────────
        if slot_id is not None:
            slot = c.execute(
                "SELECT is_booked FROM time_slots WHERE id=?", (slot_id,)
            ).fetchone()
            if not slot:
                raise ValueError("Invalid time slot.")
            if slot["is_booked"]:
                raise ValueError(
                    "This slot was just taken by another booking. "
                    "Please choose a different time."
                )
            c.execute("UPDATE time_slots SET is_booked=1 WHERE id=?", (slot_id,))

        cur = c.execute("""
            INSERT INTO bookings
              (user_id, worker_id, slot_id, category, issue, address,
               scheduled_at, payment_method, visit_charge, platform_fee, total, status)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,'pending')
        """, (user_id, worker_id, slot_id, category, issue, address,
              scheduled_at, payment_method, int(visit_charge), fee, total))
        bid = cur.lastrowid
        row = dict(c.execute("SELECT * FROM bookings WHERE id=?", (bid,)).fetchone())
    return row


def get_user_bookings(user_id):
    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        rows = c.execute("""
            SELECT b.*, w.name as worker_name, w.category as worker_cat,
                   w.photo_emoji as worker_emoji,
                   ts.slot_date, ts.slot_time
            FROM bookings b
            LEFT JOIN workers w   ON b.worker_id = w.id
            LEFT JOIN time_slots ts ON b.slot_id = ts.id
            WHERE b.user_id=?
            ORDER BY b.created_at DESC
        """, (user_id,)).fetchall()
    return [dict(r) for r in rows]


def get_worker_bookings(worker_id):
    """Fetch all bookings for a worker (for admin/worker dashboard)."""
    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        rows = c.execute("""
            SELECT b.*, u.name as user_name, u.phone as user_phone
            FROM bookings b
            LEFT JOIN users u ON b.user_id = u.id
            WHERE b.worker_id=?
            ORDER BY b.created_at DESC
        """, (worker_id,)).fetchall()
    return [dict(r) for r in rows]


def update_booking_status(booking_id: int, new_status: str) -> bool:
    """
    Update booking status. Allowed transitions:
        pending → accepted | cancelled
        accepted → completed | cancelled
        completed / cancelled → (terminal — no changes)
    Returns True on success, False if booking not found or bad transition.
    """
    VALID_STATUSES = {"pending", "accepted", "completed", "cancelled", "done", "ongoing"}
    if new_status not in VALID_STATUSES:
        return False

    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        row = c.execute("SELECT status FROM bookings WHERE id=?", (booking_id,)).fetchone()
        if not row:
            return False
        current = row["status"]
        if current in ("completed", "done", "cancelled"):
            return False   # terminal state
        c.execute("UPDATE bookings SET status=? WHERE id=?", (new_status, booking_id))

        # If completing, increment worker's total_jobs
        if new_status in ("completed", "done"):
            bk = c.execute("SELECT worker_id FROM bookings WHERE id=?", (booking_id,)).fetchone()
            if bk:
                c.execute("UPDATE workers SET total_jobs = total_jobs + 1 WHERE id=?",
                          (bk["worker_id"],))

    return True


# ── Reviews ───────────────────────────────────────────────────────────────────
def check_already_reviewed(booking_id: int) -> bool:
    """Return True if a review already exists for this booking_id."""
    with sqlite3.connect(DB, timeout=30) as c:
        row = c.execute(
            "SELECT id FROM reviews WHERE booking_id=?", (booking_id,)
        ).fetchone()
    return row is not None


def add_review(booking_id, user_id, worker_id, rating, comment, reviewer_name):
    """
    Add a review and update worker's average rating.
    Raises sqlite3.IntegrityError if the booking already has a review.
    """
    with sqlite3.connect(DB, timeout=30) as c:
        c.row_factory = sqlite3.Row
        c.execute(
            """INSERT INTO reviews (booking_id, user_id, worker_id, rating, comment, reviewer)
               VALUES (?,?,?,?,?,?)""",
            (booking_id, user_id, worker_id, int(rating), comment, reviewer_name)
        )
        # Recompute average
        stats = c.execute(
            "SELECT COUNT(*) as n, AVG(rating) as avg FROM reviews WHERE worker_id=?",
            (worker_id,)
        ).fetchone()
        c.execute(
            "UPDATE workers SET rating=ROUND(?,1), total_reviews=? WHERE id=?",
            (stats["avg"], stats["n"], worker_id)
        )