<?php
/**
 * One-time migration script to fix NULL company_id values
 *
 * Problem: All records have company_id = NULL, but queries filter by company_id = 1
 * Solution: Update all NULL company_id values to 1
 *
 * Usage: Access this file via browser: http://yourdomain.com/pos/fix_company_id.php
 * DELETE THIS FILE AFTER RUNNING IT SUCCESSFULLY
 */

// Load CodeIgniter bootstrap
define('ENVIRONMENT', isset($_SERVER['CI_ENV']) ? $_SERVER['CI_ENV'] : 'development');
define('FCPATH', __DIR__ . DIRECTORY_SEPARATOR);

// Load the framework constants
$system_path = 'system';
$application_folder = 'application';

define('SELF', pathinfo(__FILE__, PATHINFO_BASENAME));
define('BASEPATH', $system_path . '/');
define('APPPATH', $application_folder . '/');
define('VIEWPATH', $application_folder . '/views/');

// Load database config
require_once(APPPATH . 'config/tenant_databases.php');
require_once(APPPATH . 'config/database.php');

// Get the database credentials from the config
$db_config = $db['default'];

// Connect to database
$conn = new mysqli(
    $db_config['hostname'],
    $db_config['username'],
    $db_config['password'],
    $db_config['database']
);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$conn->set_charset($db_config['char_set']);

echo "<h2>Fixing NULL company_id values...</h2>";
echo "<pre>";

// Tables that have company_id column - update NULL values to 1
$tables = array(
    'db_items',
    'db_customers',
    'db_category',
    'db_brands',
    'db_units',
    'db_tax',
    'db_suppliers',
    'db_users',
    'db_sales',
    'db_salesitems',
    'db_salespayments',
    'db_salesreturn',
    'db_salesitemsreturn',
    'db_salespaymentsreturn',
    'db_purchase',
    'db_purchaseitems',
    'db_purchasepayments',
    'db_purchasereturn',
    'db_purchaseitemsreturn',
    'db_purchasepaymentsreturn',
    'db_expense',
    'db_expense_category',
    'db_cobpayments',
    'db_sobpayments',
    'db_warehouse',
    'db_roles',
    'db_permissions',
    'db_hold',
    'db_holditems',
    'db_stockentry',
    'db_smsapi',
    'db_smstemplates',
    'db_paymenttypes',
    'db_sitesettings',
    'db_customer_payments',
    'db_supplier_payments',
);

$total_updated = 0;

foreach ($tables as $table) {
    // Check if table exists and has company_id column
    $check = $conn->query("SHOW COLUMNS FROM `$table` LIKE 'company_id'");

    if ($check && $check->num_rows > 0) {
        // Count NULL records
        $count_result = $conn->query("SELECT COUNT(*) as cnt FROM `$table` WHERE company_id IS NULL");
        $null_count = $count_result->fetch_assoc()['cnt'];

        if ($null_count > 0) {
            // Update NULL company_id to 1
            $update = $conn->query("UPDATE `$table` SET company_id = 1 WHERE company_id IS NULL");

            if ($update) {
                $affected = $conn->affected_rows;
                echo "✓ $table: Updated $affected rows (was NULL → now 1)\n";
                $total_updated += $affected;
            } else {
                echo "✗ $table: ERROR - " . $conn->error . "\n";
            }
        } else {
            echo "- $table: OK (no NULL values)\n";
        }
    } else {
        echo "  $table: skipped (no company_id column)\n";
    }
}

// Also set DEFAULT value for company_id columns so new records get company_id = 1
echo "\n<h3>Setting DEFAULT values...</h3>\n";

foreach ($tables as $table) {
    $check = $conn->query("SHOW COLUMNS FROM `$table` LIKE 'company_id'");
    if ($check && $check->num_rows > 0) {
        $col = $check->fetch_assoc();
        // Set default to 1 if not already set
        $alter = $conn->query("ALTER TABLE `$table` ALTER COLUMN `company_id` SET DEFAULT 1");
        if ($alter) {
            echo "✓ $table: DEFAULT set to 1\n";
        }
    }
}

echo "\n========================================\n";
echo "DONE! Total rows updated: $total_updated\n";
echo "========================================\n";
echo "\n<b style='color:red'>⚠ DELETE THIS FILE NOW for security!</b>\n";
echo "Delete: fix_company_id.php\n";

$conn->close();
echo "</pre>";
?>
