"""
Project: Customer Service Bot
Author: RSK World
Website: https://rskworld.in
Contact: info@rskworld.com, support@rskworld.com
Phone: +91 93305 39277
Year: 2026
"""

import sqlite3

# Updated to v2 to ensure new schema is used without manual migration
DB_NAME = 'customer_service_v2.db'

def init_db(app):
    """
    Initialize the database
    Project: Customer Service Bot
    Author: RSK World
    Year: 2026
    """
    conn = sqlite3.connect(DB_NAME)
    c = conn.cursor()
    # Added priority and sentiment columns
    c.execute('''
        CREATE TABLE IF NOT EXISTS tickets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            issue TEXT NOT NULL,
            contact TEXT NOT NULL,
            status TEXT DEFAULT 'Open',
            priority TEXT DEFAULT 'Normal',
            sentiment_score REAL DEFAULT 0.0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    conn.commit()
    conn.close()

def create_ticket(issue, contact, priority='Normal', sentiment_score=0.0):
    """
    Create a new support ticket with advanced metadata
    Project: Customer Service Bot
    Author: RSK World
    Year: 2026
    """
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    c.execute('''
        INSERT INTO tickets (issue, contact, priority, sentiment_score) 
        VALUES (?, ?, ?, ?)
    ''', (issue, contact, priority, sentiment_score))
    ticket_id = c.lastrowid
    conn.commit()
    conn.close()
    return ticket_id

def get_all_tickets():
    """
    Retrieve all tickets for the admin dashboard
    """
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    c.execute('SELECT * FROM tickets ORDER BY created_at DESC')
    tickets = [dict(row) for row in c.fetchall()]
    conn.close()
    return tickets

def update_ticket_status(ticket_id, status):
    """
    Update ticket status
    """
    conn = sqlite3.connect(DB_NAME)
    c = conn.cursor()
    c.execute('UPDATE tickets SET status = ? WHERE id = ?', (status, ticket_id))
    conn.commit()
    conn.close()

def get_ticket_stats():
    """
    Get simple stats for dashboard
    """
    conn = sqlite3.connect(DB_NAME)
    c = conn.cursor()
    c.execute('SELECT COUNT(*) FROM tickets')
    total = c.fetchone()[0]
    
    c.execute("SELECT COUNT(*) FROM tickets WHERE status='Open'")
    open_count = c.fetchone()[0]
    
    c.execute("SELECT COUNT(*) FROM tickets WHERE priority='High'")
    high_priority = c.fetchone()[0]
    
    conn.close()
    return {
        'total': total,
        'open': open_count,
        'high_priority': high_priority
    }