<?php
/**
 * Migration: Add units table for measurement units
 * Run this script to add the units table to your database
 */

require_once __DIR__ . '/../config/database.php';

echo "Adding units table to database...\n";

try {
    $sql = "
        CREATE TABLE IF NOT EXISTS `units` (
          `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
          `tenant_id` INT UNSIGNED NOT NULL,
          `name` VARCHAR(50) NOT NULL,
          `abbreviation` VARCHAR(10),
          `description` TEXT,
          `is_active` TINYINT(1) DEFAULT 1,
          `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          PRIMARY KEY (`id`),
          KEY `tenant_id` (`tenant_id`),
          FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ";
    
    db()->exec($sql);
    echo "Units table created successfully!\n";
    
    // Insert some default units for existing tenants
    $stmt = db()->query("SELECT id FROM tenants");
    $tenants = $stmt->fetchAll();
    
    $defaultUnits = [
        ['Pieces', 'pcs', 'Individual pieces or items'],
        ['Kilograms', 'kg', 'Metric weight measurement'],
        ['Liters', 'L', 'Metric volume measurement'],
        ['Meters', 'm', 'Metric length measurement'],
        ['Boxes', 'box', 'Packaged in boxes'],
        ['Cartons', 'ctn', 'Packaged in cartons'],
        ['Bottles', 'btl', 'Liquid containers'],
        ['Bags', 'bag', 'Packaged in bags'],
        ['Dozens', 'doz', 'Groups of 12 items'],
        ['Grams', 'g', 'Small weight measurement']
    ];
    
    foreach ($tenants as $tenant) {
        $tenantId = $tenant['id'];
        
        // Check if units already exist for this tenant
        $checkStmt = db()->prepare("SELECT COUNT(*) FROM units WHERE tenant_id = ?");
        $checkStmt->execute([$tenantId]);
        $count = $checkStmt->fetchColumn();
        
        if ($count == 0) {
            foreach ($defaultUnits as $unit) {
                $insertStmt = db()->prepare(
                    "INSERT INTO units (tenant_id, name, abbreviation, description) VALUES (?, ?, ?, ?)"
                );
                $insertStmt->execute([$tenantId, $unit[0], $unit[1], $unit[2]]);
            }
            echo "Default units added for tenant ID: {$tenantId}\n";
        }
    }
    
    echo "Migration completed successfully!\n";
    
} catch (Exception $e) {
    echo "Migration failed: " . $e->getMessage() . "\n";
    exit(1);
}
