<?php
/**
 * Ghalla Mandi ERP — Proper Urdu Seed Data & Encoding Fix
 */

header('Content-Type: text/plain; charset=utf-8');

try {
    $dsn = "mysql:host=localhost;dbname=naveed_trader_db;charset=utf8mb4";
    $db = new PDO($dsn, "root", "", [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
    ]);

    echo "Connected successfully to naveed_trader_db\n";

    // 1. Fix Database and Table Collation
    echo "Fixing collations to utf8mb4_unicode_ci...\n";
    $db->exec("ALTER DATABASE naveed_trader_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    
    $tables = $db->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    foreach ($tables as $table) {
        $db->exec("ALTER TABLE `$table` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    }

    // 2. Clear existing dynamic data (but keep system accounts and admin)
    echo "Cleaning up old data...\n";
    $db->exec("SET FOREIGN_KEY_CHECKS = 0");
    $db->exec("TRUNCATE TABLE purchase_lots");
    $db->exec("TRUNCATE TABLE purchase_deductions");
    $db->exec("TRUNCATE TABLE purchase_master");
    $db->exec("TRUNCATE TABLE sale_additions");
    $db->exec("TRUNCATE TABLE sale_master");
    $db->exec("TRUNCATE TABLE journal_entries");
    $db->exec("TRUNCATE TABLE journals");
    
    // Delete non-system accounts and items
    $db->exec("DELETE FROM accounts WHERE is_system = 0");
    $db->exec("DELETE FROM items");
    $db->exec("SET FOREIGN_KEY_CHECKS = 1");

    // 3. Add Proper Urdu Items
    echo "Adding Items...\n";
    $items = [
        ['گندم (Kanak)', 'KG'],
        ['مکئی (Makai)', 'KG'],
        ['سرسوں (Sarson)', 'KG'],
        ['چنا (Chana)', 'KG'],
        ['باجرہ (Bajra)', 'KG'],
        ['مونجی (Rice Paddy)', 'KG'],
        ['کپاس (Cotton)', 'KG'],
        ['سورج مکھی (Sunflower)', 'KG']
    ];
    $stmt = $db->prepare("INSERT INTO items (name, unit) VALUES (?, ?)");
    foreach ($items as $item) {
        $stmt->execute($item);
    }

    // 4. Add Proper Urdu Accounts (Parties/Banks)
    echo "Adding Parties & Accounts...\n";
    $accounts = [
        // Zamindars (Farmers)
        ['چوہدری اصغر علی', 'party', 'zamidar', '0300-1234567', 'پاکپتن'],
        ['حاجی بشیر احمد', 'party', 'zamidar', '0301-7654321', 'عارف والا'],
        ['سیٹھ طارق محمود', 'party', 'zamidar', '0302-1122334', 'ساہیوال'],
        ['راؤ ساجد علی', 'party', 'zamidar', '0345-9988776', 'بورے والا'],
        
        // Khareedars (Buyers)
        ['میاں برادرز فلور مل', 'party', 'khareedar', '042-3588990', 'لاہور'],
        ['اتفاق ٹریڈرز', 'party', 'khareedar', '0321-4455667', 'فیصل آباد'],
        ['نوید سیڈ کارپوریشن', 'party', 'khareedar', '0333-8877665', 'ملتان'],
        ['الرحیم فوڈز', 'party', 'khareedar', '0300-5566778', 'کراچی'],

        // Banks
        ['میزان بینک (Meezan)', 'bank_cash', 'bank', null, 'پاکپتن'],
        ['ایچ بی ایل (HBL)', 'bank_cash', 'bank', null, 'عارف والا'],
        ['نیشنل بینک (NBP)', 'bank_cash', 'bank', null, 'ساہیوال']
    ];

    $stmt = $db->prepare("INSERT INTO accounts (name, type, sub_type, phone, city) VALUES (?, ?, ?, ?, ?)");
    foreach ($accounts as $acc) {
        $stmt->execute($acc);
    }

    echo "\nSUCCESS! Urdu test data added properly with utf8mb4 encoding.\n";

} catch (PDOException $e) {
    echo "ERROR: " . $e->getMessage() . "\n";
}
