ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

if (!extension_loaded('mysqli')) {
    die("Error: The 'mysqli' PHP extension is not installed or enabled on this server.");
}

function df_load_env() {
    $envPath = __DIR__ . '/../.env';
    
    // Fallback if __DIR__ fails on some hostings
    if (!file_exists($envPath)) {
        $envPath = $_SERVER['DOCUMENT_ROOT'] . '/.env';
    }
    
    if (!file_exists($envPath)) return [];
    
    $lines = file($envPath, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    $env = [];
    foreach ($lines as $line) {
        $line = trim($line);
        if ($line === '' || $line[0] === '#') continue;
        $parts = explode('=', $line, 2);
        if (count($parts) === 2) {
            $env[trim($parts[0])] = trim($parts[1], " \"'");
        }
    }
    return $env;
}

$env = df_load_env();

try {
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
    $conn = new mysqli(
        $env['DB_HOST'] ?? '127.0.0.1',
        $env['DB_USERNAME'] ?? '',
        $env['DB_PASSWORD'] ?? '',
        $env['DB_DATABASE'] ?? '',
        (int)($env['DB_PORT'] ?? 3306)
    );
    $conn->set_charset("utf8mb4");
} catch (Exception $e) {
    die("Database Connection Failed: " . $e->getMessage());
}

function df_table($name) {
    global $env;
    $prefix = $env['DB_PREFIX'] ?? ($env['APP_PREFIX'] ?? 'df_');
    return $prefix . $name;
}

function df_auto_setup() {
    global $conn;
    
    // 1. Core Identity & RBAC
    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('users') . " (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `username` VARCHAR(50) UNIQUE NOT NULL,
        `password` VARCHAR(255) NOT NULL,
        `full_name` VARCHAR(100),
        `mobile` VARCHAR(15),
        `role` ENUM('admin', 'salesman', 'delivery', 'factory') DEFAULT 'salesman',
        `status` TINYINT DEFAULT 1,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // 2. Catalog & Inventory
    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('categories') . " (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(100) NOT NULL,
        `status` TINYINT DEFAULT 1
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('products') . " (
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(100),
        `image` VARCHAR(255),
        `image_id` BIGINT DEFAULT 0,
        `status` TINYINT DEFAULT 1,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('qualities') . " (
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `product_id` BIGINT UNSIGNED,
        `quality_name` VARCHAR(50),
        `price_per_kg` DECIMAL(10,2),
        `stock_kg` DECIMAL(10,3),
        `status` TINYINT DEFAULT 1,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('inventory_stock') . " (
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `quality_id` BIGINT UNSIGNED NOT NULL UNIQUE,
        `current_qty` DECIMAL(12,3) DEFAULT 0,
        `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('inventory_logs') . " (
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `quality_id` BIGINT UNSIGNED NOT NULL,
        `qty` DECIMAL(12,3) NOT NULL,
        `type` ENUM('sale', 'purchase', 'manual_add', 'manual_reduce', 'return') NOT NULL,
        `note` TEXT,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // 3. Sales & Fulfilment
    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('customers') . " (
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(255),
        `mobile` VARCHAR(15) UNIQUE,
        `whatsapp` VARCHAR(15),
        `address` TEXT,
        `city` VARCHAR(100),
        `pincode` VARCHAR(10),
        `notes` TEXT,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('bills') . " (
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `bill_no` VARCHAR(30),
        `customer_id` BIGINT UNSIGNED,
        `salesman_id` BIGINT UNSIGNED,
        `total` DECIMAL(10,2),
        `paid_via` TINYINT DEFAULT 1,
        `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('bill_items') . " (
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `bill_id` BIGINT UNSIGNED,
        `quality_id` BIGINT UNSIGNED,
        `qty` DECIMAL(10,3),
        `rate` DECIMAL(10,2),
        `amount` DECIMAL(10,2),
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('orders') . " (
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `customer_id` BIGINT UNSIGNED NOT NULL,
        `order_source` ENUM('website', 'pos', 'manual') DEFAULT 'pos',
        `status` ENUM('pending', 'confirmed', 'ready', 'picked_up', 'out_for_delivery', 'delivered', 'cancelled') DEFAULT 'pending',
        `total_amount` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
        `delivery_address` TEXT NOT NULL,
        `lat` DECIMAL(10,8) DEFAULT NULL,
        `lng` DECIMAL(11,8) DEFAULT NULL,
        `delivery_boy_id` BIGINT UNSIGNED DEFAULT NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('order_items') . " (
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
        `order_id` BIGINT UNSIGNED,
        `quality_id` BIGINT UNSIGNED,
        `qty` DECIMAL(10,3),
        `rate` DECIMAL(10,2),
        `amount` DECIMAL(10,2),
        PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // 4. Logistics & WhatsApp
    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('mds_whatsapp_queue') . " (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `mobile` VARCHAR(20) NOT NULL,
        `message` TEXT NOT NULL,
        `status` ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // 5. HRM & Accounting
    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('hrm_staff') . " (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(100) NOT NULL,
        `mobile` VARCHAR(15) UNIQUE,
        `monthly_salary` DECIMAL(10,2),
        `join_date` DATE,
        `status` ENUM('active', 'inactive') DEFAULT 'active',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('hrm_ledger') . " (
        `id` INT AUTO_INCREMENT PRIMARY KEY,
        `staff_id` INT NOT NULL,
        `amount` DECIMAL(10,2) NOT NULL,
        `type` ENUM('salary', 'expense', 'bonus', 'settlement') DEFAULT 'expense',
        `note` TEXT,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('expense_categories') . " (
        `id` BIGINT UNSIGNED AUTO_INCREMENT,
        `name` VARCHAR(100) NOT NULL,
        `status` TINYINT DEFAULT 1,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('expenses') . " (
        `id` BIGINT UNSIGNED AUTO_INCREMENT,
        `category_id` BIGINT UNSIGNED NOT NULL,
        `amount` DECIMAL(12,2) NOT NULL,
        `vendor_name` VARCHAR(255) DEFAULT NULL,
        `note` TEXT,
        `paid_via` VARCHAR(50) DEFAULT 'cash',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('purchases') . " (
        `id` BIGINT UNSIGNED AUTO_INCREMENT,
        `supplier_name` VARCHAR(255) DEFAULT NULL,
        `total_amount` DECIMAL(12,2) NOT NULL,
        `paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0,
        `status` VARCHAR(20) DEFAULT 'paid',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // 6. Manufacturing
    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('manufacturing') . " (
        `id` BIGINT UNSIGNED AUTO_INCREMENT,
        `batch_no` VARCHAR(50) NOT NULL,
        `finished_quality_id` BIGINT UNSIGNED NOT NULL,
        `qty_produced` DECIMAL(12,3) NOT NULL,
        `status` ENUM('planned', 'in_progress', 'completed') DEFAULT 'planned',
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    $conn->query("CREATE TABLE IF NOT EXISTS " . df_table('manufacturing_ingredients') . " (
        `id` BIGINT UNSIGNED AUTO_INCREMENT,
        `manufacturing_id` BIGINT UNSIGNED NOT NULL,
        `quality_id` BIGINT UNSIGNED NOT NULL,
        `qty_used` DECIMAL(12,3) NOT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Add default admin (Safe because of IF NOT EXISTS and username UNIQUE)
    $pass = password_hash('admin123', PASSWORD_DEFAULT);
    $conn->query("INSERT IGNORE INTO " . df_table('users') . " (username, password, full_name, role) VALUES ('admin', '$pass', 'Administrator', 'admin')");
}

df_auto_setup();
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Mateshwari DryFruits</title>
    <link href="https://fonts.googleapis.com/css2?family=Outfit:wght@400;600;800&display=swap" rel="stylesheet">
    <script src="https://unpkg.com/lucide@latest"></script>
    <style>
        :root { --brand: #f59e0b; --bg: #fff; --text: #1d1d1f; }
        body { font-family: 'Outfit', sans-serif; background: var(--bg); color: var(--text); margin: 0; }
        header { padding: 20px; display: flex; justify-content: space-between; align-items: center; border-bottom: 1px solid #eee; }
        .logo { font-size: 24px; font-weight: 800; color: var(--brand); }
        .nav-icons { display: flex; gap: 20px; }
        .container { max-width: 1200px; margin: 0 auto; padding: 20px; }
    </style>
</head>
<body>
    <header>
        <div class="logo">Mateshwari</div>
        <div class="nav-icons">
            <i data-lucide="search"></i>
            <i data-lucide="shopping-bag"></i>
            <i data-lucide="user"></i>
        </div>
    </header>

    <main class="container">
        <h1>Welcome to Mateshwari</h1><p>Our premium dry fruits collection is coming soon to this standalone portal.</p>    </main>

    <script>lucide.createIcons();</script>
</body>
</html>