"""
Módulo de Banco de Dados SQLite para a Plataforma SaaS Multi-Tenancy de Barbearias.
Modo WAL ativado para alta performance e concorrência segura.
"""
import sqlite3
import os
from contextlib import contextmanager

DB_PATH = os.path.join(os.path.dirname(os.path.dirname(__file__)), "barber.db")

def get_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON;")
    conn.execute("PRAGMA journal_mode = WAL;")
    return conn

@contextmanager
def get_db():
    conn = get_connection()
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

def init_db():
    with get_db() as conn:
        cursor = conn.cursor()
        
        # 1. Barbearias (Multi-tenancy real com Planos SaaS: basico, intermediario, profissional)
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS barbershops (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            slug TEXT UNIQUE NOT NULL,
            phone TEXT NOT NULL,
            email TEXT UNIQUE,
            password TEXT DEFAULT '123456',
            plan_tier TEXT DEFAULT 'basico', -- 'basico', 'intermediario', 'profissional'
            max_barbers INTEGER DEFAULT 1,
            is_active INTEGER DEFAULT 1,
            opening_time TEXT DEFAULT '09:00',
            closing_time TEXT DEFAULT '20:00',
            slot_interval INTEGER DEFAULT 30,
            pix_key TEXT,
            pix_deposit_amount REAL DEFAULT 0.0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        """)

        # 2. Barbeiros / Profissionais da equipe
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS barbers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            barbershop_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            nickname TEXT,
            phone TEXT,
            avatar_color TEXT DEFAULT '#d97706',
            commission_rate REAL DEFAULT 0.50,
            is_active INTEGER DEFAULT 1,
            start_time TEXT DEFAULT '09:00',
            end_time TEXT DEFAULT '20:00',
            working_days TEXT DEFAULT '1,2,3,4,5,6',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (barbershop_id) REFERENCES barbershops (id) ON DELETE CASCADE
        );
        """)

        # 3. Serviços oferecidos
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS services (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            barbershop_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            description TEXT,
            price REAL NOT NULL,
            duration_minutes INTEGER DEFAULT 30,
            category TEXT DEFAULT 'Cabelo',
            is_active INTEGER DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (barbershop_id) REFERENCES barbershops (id) ON DELETE CASCADE
        );
        """)

        # 4. Clientes (identificados por telefone - sem necessidade de senha para agendar)
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS clients (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            barbershop_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            phone TEXT NOT NULL,
            notes TEXT,
            total_visits INTEGER DEFAULT 0,
            last_visit_at TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(barbershop_id, phone),
            FOREIGN KEY (barbershop_id) REFERENCES barbershops (id) ON DELETE CASCADE
        );
        """)

        # 5. Agendamentos
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS appointments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            barbershop_id INTEGER NOT NULL,
            barber_id INTEGER NOT NULL,
            client_id INTEGER NOT NULL,
            service_id INTEGER NOT NULL,
            appointment_date TEXT NOT NULL,
            start_time TEXT NOT NULL,
            end_time TEXT NOT NULL,
            status TEXT DEFAULT 'agendado', -- agendado, confirmado, em_atendimento, concluido, cancelado, falta
            total_amount REAL NOT NULL,
            deposit_paid REAL DEFAULT 0.0,
            whatsapp_reminded INTEGER DEFAULT 0,
            notes TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (barbershop_id) REFERENCES barbershops (id) ON DELETE CASCADE,
            FOREIGN KEY (barber_id) REFERENCES barbers (id) ON DELETE CASCADE,
            FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE,
            FOREIGN KEY (service_id) REFERENCES services (id) ON DELETE CASCADE
        );
        """)

        # 6. Produtos para venda de bancada (Pomadas, óleos, bebidas)
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            barbershop_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            price REAL NOT NULL,
            cost_price REAL DEFAULT 0.0,
            stock_quantity INTEGER DEFAULT 10,
            is_active INTEGER DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (barbershop_id) REFERENCES barbershops (id) ON DELETE CASCADE
        );
        """)

        # 7. Comandas / Vendas do Caixa
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            barbershop_id INTEGER NOT NULL,
            appointment_id INTEGER,
            barber_id INTEGER NOT NULL,
            client_id INTEGER,
            client_name TEXT,
            total_services REAL DEFAULT 0.0,
            total_products REAL DEFAULT 0.0,
            discount REAL DEFAULT 0.0,
            final_amount REAL NOT NULL,
            payment_method TEXT DEFAULT 'pix', -- pix, cartao_credito, cartao_debito, dinheiro, fiado
            payment_status TEXT DEFAULT 'pago', -- pago, pendente, cancelado
            barber_commission_amount REAL DEFAULT 0.0,
            house_net_amount REAL DEFAULT 0.0,
            items_summary TEXT,
            notes TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (barbershop_id) REFERENCES barbershops (id) ON DELETE CASCADE,
            FOREIGN KEY (appointment_id) REFERENCES appointments (id) ON DELETE SET NULL,
            FOREIGN KEY (barber_id) REFERENCES barbers (id) ON DELETE CASCADE
        );
        """)

        # 8. Itens da comanda
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS order_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL,
            item_type TEXT NOT NULL,
            item_id INTEGER,
            item_name TEXT NOT NULL,
            price REAL NOT NULL,
            quantity INTEGER DEFAULT 1,
            FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
        );
        """)

        # 9. Vales e adiantamentos
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS barber_advances (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            barbershop_id INTEGER NOT NULL,
            barber_id INTEGER NOT NULL,
            amount REAL NOT NULL,
            description TEXT,
            date TEXT NOT NULL,
            is_deducted INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (barbershop_id) REFERENCES barbershops (id) ON DELETE CASCADE,
            FOREIGN KEY (barber_id) REFERENCES barbers (id) ON DELETE CASCADE
        );
        """)

        # 10. Planos de Assinatura do Cliente (Clube)
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS subscription_plans (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            barbershop_id INTEGER NOT NULL,
            name TEXT NOT NULL,
            monthly_price REAL NOT NULL,
            cuts_included INTEGER DEFAULT 4,
            description TEXT,
            is_active INTEGER DEFAULT 1,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (barbershop_id) REFERENCES barbershops (id) ON DELETE CASCADE
        );
        """)

        # 11. Assinaturas de Clientes no Clube
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS client_subscriptions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            barbershop_id INTEGER NOT NULL,
            client_id INTEGER NOT NULL,
            plan_id INTEGER NOT NULL,
            status TEXT DEFAULT 'ativo',
            cuts_used_this_month INTEGER DEFAULT 0,
            started_at TEXT NOT NULL,
            next_billing_date TEXT NOT NULL,
            FOREIGN KEY (barbershop_id) REFERENCES barbershops (id) ON DELETE CASCADE,
            FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE CASCADE,
            FOREIGN KEY (plan_id) REFERENCES subscription_plans (id) ON DELETE CASCADE
        );
        """)

        # 12. Mensagens de WhatsApp
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS whatsapp_messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            barbershop_id INTEGER NOT NULL,
            appointment_id INTEGER,
            client_phone TEXT NOT NULL,
            client_name TEXT NOT NULL,
            message_type TEXT NOT NULL,
            message_text TEXT NOT NULL,
            status TEXT DEFAULT 'enviada',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (barbershop_id) REFERENCES barbershops (id) ON DELETE CASCADE
        );
        """)