import os
import sqlite3
from datetime import datetime, timedelta

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_NAME = os.path.join(BASE_DIR, "electricol.db")

print(f"📁 Database file path locked to: {DB_NAME}")

MASTER_ADMIN_CODES = [
    "VoltX_99x_SecureCore",
    "AlphaNode_77#_Gatekeeper",
    "CyberSpace_44!_Clearance",
    "HighVolt_22$_Operational",
    "Electricol_Root_88%_Master"
]

def init_database_system():
    """
    Executes on application startup to ensure the SQLite schema is intact.
    Guarantees tables, relations, and initial folders match runtime expectations.
    """
    conn = sqlite3.connect(DB_NAME)
    cursor = conn.cursor()
    
    print("⚙️ Initiating Project Electricol structural layout check...")

    # 1. PROFILE NODES (USERS)
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL,
        password_hash TEXT NOT NULL,
        wallet_balance REAL DEFAULT 0.00,
        role TEXT DEFAULT 'user'
    )
    """)

    # 2. MARKETPLACE CATALOG (FILES)
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS files (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        base_price REAL DEFAULT 2.00,
        secure_file_path TEXT NOT NULL,
        collection TEXT DEFAULT 'General Patches'
    )
    """)

    # 3. TRANSACTION TRACKING (PURCHASES)
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS purchases (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        file_id INTEGER NOT NULL,
        package_type TEXT NOT NULL,
        price_paid REAL NOT NULL,
        purchased_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        expires_at TIMESTAMP,
        FOREIGN KEY(user_id) REFERENCES users(id),
        FOREIGN KEY(file_id) REFERENCES files(id)
    )
    """)

    # 4. CUSTOM COLLECTION FOLDERS SCHEMA TRACKING LAYER
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS collections (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE NOT NULL
    )
    """)
    
    # Inject default tracking collections if the system environment is newly initialized
    cursor.execute("SELECT COUNT(*) FROM collections")
    if cursor.fetchone()[0] == 0:
        default_folders = [
            ("General Patches",),
            ("Android Customization",),
            ("Solar Energy Systems",),
            ("Digital Store Asset",)
        ]
        cursor.executemany("INSERT INTO collections (name) VALUES (?)", default_folders)
        print("📁 Database Schema: Initial collection folder categories pre-populated.")
    
    conn.commit()
    conn.close()
    print("✅ System Core: All database structural bounds synchronized perfectly.")


def calculate_tier_pricing_and_expiration(base_price: float, package_type: str):
    """
    Helper matrix determining tier pricing shifts and access expiration limits.
    """
    now = datetime.utcnow()
    
    matrix = {
        "1_time":   (base_price, now + timedelta(minutes=5)),
        "24_hours": (base_price + 1.00, now + timedelta(days=1)),
        "1_month":  (base_price + 2.00, now + timedelta(days=30)),
        "1_year":   (base_price + 3.00, now + timedelta(days=365)),
        "5_years":  (base_price + 4.00, now + timedelta(days=1825)),
        "forever":  (base_price + 5.00, None)
    }
    
    if package_type not in matrix:
        raise ValueError(f"Invalid tier authorization parameter: {package_type}")
        
    return matrix[package_type]

if __name__ == "__main__":
    init_database_system()