"""
AutoLyrics OneClick - SQLite Database & CSV Synchronization Engine
"""

import os
import sqlite3
import csv
from datetime import datetime

DB_FILE = os.path.join(os.path.dirname(__file__), 'orders.db')
CSV_FILE = os.path.join(os.path.dirname(__file__), 'orders.csv')

CSV_HEADERS = [
    'Order ID',
    'Date & Time',
    'Customer Name',
    'Customer Email',
    'Base Price ($)',
    'Tip Amount ($)',
    'Total Paid ($)',
    'Payment Method',
    'Payment Status',
    'License Key',
    'Download Token'
]

def get_db_connection():
    conn = sqlite3.connect(DB_FILE)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    """Initialize SQLite database and ensure orders.csv exists with headers."""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id TEXT UNIQUE NOT NULL,
            customer_name TEXT,
            customer_email TEXT NOT NULL,
            base_price REAL NOT NULL DEFAULT 2.99,
            tip_amount REAL NOT NULL DEFAULT 0.00,
            total_amount REAL NOT NULL,
            payment_method TEXT NOT NULL,
            payment_status TEXT NOT NULL,
            license_key TEXT UNIQUE NOT NULL,
            download_token TEXT UNIQUE NOT NULL,
            download_count INTEGER DEFAULT 0,
            ip_address TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    conn.commit()
    conn.close()

    # Ensure CSV file has headers
    if not os.path.exists(CSV_FILE) or os.path.getsize(CSV_FILE) == 0:
        with open(CSV_FILE, mode='w', newline='', encoding='utf-8') as f:
            writer = csv.writer(f)
            writer.writerow(CSV_HEADERS)

def save_order(order_data):
    """
    Save an order to both SQLite database and append to orders.csv
    order_data dict keys:
      order_id, customer_name, customer_email, base_price, tip_amount,
      total_amount, payment_method, payment_status, license_key, download_token, ip_address
    """
    now_str = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    
    # 1. Save to SQLite
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO orders (
            order_id, customer_name, customer_email, base_price, tip_amount,
            total_amount, payment_method, payment_status, license_key, download_token,
            ip_address, created_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', (
        order_data['order_id'],
        order_data.get('customer_name', 'Anonymous'),
        order_data['customer_email'],
        float(order_data.get('base_price', 2.99)),
        float(order_data.get('tip_amount', 0.00)),
        float(order_data['total_amount']),
        order_data.get('payment_method', 'Card'),
        order_data.get('payment_status', 'COMPLETED'),
        order_data['license_key'],
        order_data['download_token'],
        order_data.get('ip_address', '127.0.0.1'),
        now_str
    ))
    conn.commit()
    conn.close()

    # 2. Append to CSV for easy inspection in Excel / Sheets
    row = [
        order_data['order_id'],
        now_str,
        order_data.get('customer_name', 'Anonymous'),
        order_data['customer_email'],
        f"{float(order_data.get('base_price', 2.99)):.2f}",
        f"{float(order_data.get('tip_amount', 0.00)):.2f}",
        f"{float(order_data['total_amount']):.2f}",
        order_data.get('payment_method', 'Card'),
        order_data.get('payment_status', 'COMPLETED'),
        order_data['license_key'],
        order_data['download_token']
    ]

    with open(CSV_FILE, mode='a', newline='', encoding='utf-8') as f:
        writer = csv.writer(f)
        writer.writerow(row)

    return True

def get_all_orders():
    """Retrieve all orders from SQLite database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM orders ORDER BY id DESC')
    rows = cursor.fetchall()
    conn.close()
    return [dict(row) for row in rows]

def get_order_by_token(token):
    """Retrieve an order by its secure download token."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM orders WHERE download_token = ?', (token,))
    row = cursor.fetchone()
    if row:
        cursor.execute('UPDATE orders SET download_count = download_count + 1 WHERE download_token = ?', (token,))
        conn.commit()
    conn.close()
    return dict(row) if row else None

def get_sales_stats():
    """Calculate overall revenue, total tips, and orders count."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('''
        SELECT 
            COUNT(*) as total_orders,
            COALESCE(SUM(total_amount), 0) as total_revenue,
            COALESCE(SUM(tip_amount), 0) as total_tips,
            COALESCE(SUM(base_price), 0) as base_revenue
        FROM orders WHERE payment_status = 'COMPLETED'
    ''')
    stats = cursor.fetchone()
    conn.close()
    return dict(stats) if stats else {
        'total_orders': 0,
        'total_revenue': 0.0,
        'total_tips': 0.0,
        'base_revenue': 0.0
    }

def get_orders_by_email(email):
    """Retrieve all completed orders for a customer email (case-insensitive)."""
    if not email:
        return []
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('''
        SELECT * FROM orders 
        WHERE LOWER(TRIM(customer_email)) = LOWER(TRIM(?))
        ORDER BY id DESC
    ''', (email,))
    rows = cursor.fetchall()
    conn.close()
    return [dict(row) for row in rows]

def get_order_by_id(order_id):
    """Retrieve order by order_id."""
    if not order_id:
        return None
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM orders WHERE UPPER(TRIM(order_id)) = UPPER(TRIM(?))', (order_id.strip(),))
    row = cursor.fetchone()
    conn.close()
    return dict(row) if row else None

def get_order_by_key(license_key):
    """Retrieve order by license key."""
    if not license_key:
        return None
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM orders WHERE UPPER(TRIM(license_key)) = UPPER(TRIM(?))', (license_key.strip(),))
    row = cursor.fetchone()
    conn.close()
    return dict(row) if row else None