# erp_db_init.py
import sqlite3
import os
import pathlib

def init_sqlite_db(db_path='./data/erp_db.sqlite'):
    """Initialize SQLite database with ERP tables."""
    try:
        # Create the database directory if it doesn't exist
        db_dir = pathlib.Path(os.path.dirname(db_path))
        db_dir.mkdir(exist_ok=True)
        
        # Connect to SQLite database (will be created if it doesn't exist)
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Enable foreign keys
        conn.execute("PRAGMA foreign_keys = ON")
        
        # Create customers table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS erp_customers (
            customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT NOT NULL UNIQUE,
            phone TEXT,
            address TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        
        # Create products table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS erp_products (
            product_id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_name TEXT NOT NULL,
            description TEXT,
            category TEXT,
            price REAL NOT NULL,
            stock_quantity INTEGER NOT NULL DEFAULT 0,
            sku TEXT UNIQUE
        )
        ''')
        
        # Create orders table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS erp_orders (
            order_id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_id INTEGER,
            order_date DATE DEFAULT CURRENT_DATE,
            total_amount REAL NOT NULL,
            status TEXT NOT NULL DEFAULT 'Processing',
            previous_order_id INTEGER,
            estimated_delivery DATE,
            actual_delivery DATE,
            payment_status TEXT DEFAULT 'Pending',
            shipping_address TEXT NOT NULL,
            shipping_country TEXT,
            destination_country TEXT,
            FOREIGN KEY (customer_id) REFERENCES erp_customers (customer_id),
            FOREIGN KEY (previous_order_id) REFERENCES erp_orders (order_id)
        )
        ''')
        
        # Create order items table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS erp_order_items (
            order_item_id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER,
            product_id INTEGER,
            quantity INTEGER NOT NULL,
            unit_price REAL NOT NULL,
            subtotal REAL NOT NULL,
            FOREIGN KEY (order_id) REFERENCES erp_orders (order_id),
            FOREIGN KEY (product_id) REFERENCES erp_products (product_id)
        )
        ''')
        
        # Create order history table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS erp_order_history (
            history_id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            status_change TEXT,
            notes TEXT,
            updated_by TEXT,
            FOREIGN KEY (order_id) REFERENCES erp_orders (order_id)
        )
        ''')
        
        # Create invoices table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS erp_invoices (
            invoice_id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER,
            invoice_date DATE DEFAULT CURRENT_DATE,
            amount REAL NOT NULL,
            payment_terms TEXT,
            due_date DATE,
            is_paid BOOLEAN DEFAULT 0,
            invoice_number TEXT UNIQUE,
            FOREIGN KEY (order_id) REFERENCES erp_orders (order_id)
        )
        ''')
        
        # Create global disruptions table
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS live_global_disruptions (
            disruption_id INTEGER PRIMARY KEY AUTOINCREMENT,
            source_country TEXT NOT NULL,
            destination_country TEXT NOT NULL,
            disruption_type TEXT NOT NULL,
            severity INTEGER NOT NULL CHECK (severity >= 1 AND severity <= 5),
            start_date DATE NOT NULL,
            expected_end_date DATE,
            actual_end_date DATE,
            is_active BOOLEAN DEFAULT 1,
            description TEXT,
            impact_hours INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE (source_country, destination_country, disruption_type, start_date)
        )
        ''')
        
        # Create indexes
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_orders_customer ON erp_orders (customer_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_order_items_order ON erp_order_items (order_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_order_items_product ON erp_order_items (product_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_order_history_order ON erp_order_history (order_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_invoices_order ON erp_invoices (order_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_global_disruptions_active ON live_global_disruptions (is_active)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_global_disruptions_countries ON live_global_disruptions (source_country, destination_country)')
        
        conn.commit()
        conn.close()
        
        return True
    except Exception as e:
        print(f"Error initializing SQLite database: {str(e)}")
        return False

if __name__ == "__main__":
    init_sqlite_db()