import os
import sqlite3
from contextlib import contextmanager
from datetime import datetime
from modules.logger import logger

DB_DIR = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), 'database')
DB_FILE = os.path.join(DB_DIR, 'database.db')

def init_db():
    """Initializes the database and creates tables if they do not exist."""
    if not os.path.exists(DB_DIR):
        os.makedirs(DB_DIR, exist_ok=True)
        
    logger.info("Initializing SQLite database...")
    
    with get_db() as conn:
        cursor = conn.cursor()
        
        # 1. Admin Table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS admin (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL,
                password_hash TEXT NOT NULL,
                email TEXT NOT NULL,
                timezone TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 2. Schedules Table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS schedules (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                start_time TEXT NOT NULL,      -- Format: "HH:MM" e.g., "23:00"
                end_time TEXT NOT NULL,        -- Format: "HH:MM" e.g., "08:00"
                timezone TEXT NOT NULL,        -- e.g., "Asia/Dhaka"
                days TEXT NOT NULL,            -- Comma-separated: "Saturday,Sunday,Monday..."
                enabled INTEGER DEFAULT 1      -- 1 = Enabled, 0 = Disabled
            )
        ''')
        
        # 3. Autoreply Logs Table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS autoreply_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                user_id INTEGER,
                username TEXT,
                full_name TEXT,
                original_message TEXT,
                reply_message TEXT,
                status TEXT NOT NULL           -- "Replied", "Ignored: Keyword", "Ignored: VIP", "Ignored: Reply Once", "Error"
            )
        ''')
        
        # 4. Login Logs Table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS login_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                username TEXT,
                ip_address TEXT,
                status TEXT NOT NULL           -- "Success", "Failed"
            )
        ''')
        
        # 5. VIP List Table (Whitelist/Blacklist)
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS vip_list (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                list_type TEXT CHECK(list_type IN ('whitelist', 'blacklist')) NOT NULL,
                identifier TEXT UNIQUE NOT NULL, -- Username, Phone, or User ID
                note TEXT
            )
        ''')
        
        # 6. Keywords Ignore Table
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS keywords_ignore (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                keyword TEXT UNIQUE NOT NULL
            )
        ''')
        
        conn.commit()
    logger.info("Database tables verified/created successfully.")

@contextmanager
def get_db():
    """Context manager for database connections."""
    conn = sqlite3.connect(DB_FILE, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    try:
        yield conn
    except Exception as e:
        conn.rollback()
        logger.error(f"Database error: {e}")
        raise e
    finally:
        conn.close()

def query_db(query, args=(), one=False):
    """Executes a query and returns a single row or list of rows."""
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(query, args)
        rv = cur.fetchall()
        return (rv[0] if rv else None) if one else rv

def execute_db(query, args=()):
    """Executes a query and commits changes."""
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(query, args)
        conn.commit()
        return cur.lastrowid