<?php
/**
 * One-time script to create inventory_reports table
 * Run once: php database/create_inventory_reports.php
 */
require_once __DIR__ . '/../includes/init.php';

$db = Database::getInstance();

    $sql = "
-- inventory_reports table (no FK to avoid issues)
CREATE TABLE IF NOT EXISTS `inventory_reports` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `report_month` DATE NOT NULL, 
    `item_code` VARCHAR(50),
    `opening_qty` DECIMAL(15, 3) DEFAULT 0,
    `receipts_qty` DECIMAL(15, 3) DEFAULT 0,
    `issues_qty` DECIMAL(15, 3) DEFAULT 0,
    `system_qty` DECIMAL(15, 3) GENERATED ALWAYS AS (`opening_qty` + `receipts_qty` - `issues_qty`) STORED,
    `count_qty` DECIMAL(15, 3) DEFAULT 0,
    `variance_qty` DECIMAL(15, 3) GENERATED ALWAYS AS (`count_qty` - (`opening_qty` + `receipts_qty` - `issues_qty`)) STORED,
    `variance_val` DECIMAL(15, 2) DEFAULT 0, 
    `loss_nature` VARCHAR(100),
    `comment` TEXT,
    `action_taken` TEXT,
    `responsible_person` VARCHAR(100),
    `risk_rating` ENUM('Low', 'Medium', 'High', 'Critical'),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY `idx_report_month` (`report_month`),
    KEY `idx_item_code` (`item_code`),
    INDEX `idx_item_code` (`item_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Views
CREATE OR REPLACE VIEW `variance_summary` AS
SELECT 
    r.report_month,
    r.item_code,
    i.Item_Description as item_desc,
    i.Uom as uom,
    r.opening_qty,
    r.receipts_qty,
    r.issues_qty,
    r.system_qty,
    r.count_qty,
    r.variance_qty,
    r.variance_val,
    CASE WHEN r.system_qty != 0 THEN (r.variance_qty / r.system_qty) * 100 ELSE 0 END AS variance_percentage,
    0.5 as expected_loss_pct,
    r.risk_rating,
    r.responsible_person
FROM inventory_reports r
LEFT JOIN items i ON r.item_code = i.Item_Code;

CREATE OR REPLACE VIEW `high_risk_exceptions` AS
SELECT 
    item_code, 
    system_qty, 
    count_qty, 
    variance_qty, 
    risk_rating, 
    comment, 
    action_taken
FROM inventory_reports
WHERE risk_rating IN ('High', 'Critical');
";

try {
    $db->getPdo()->exec($sql);
    echo "SUCCESS: inventory_reports table and views created!\n";
    echo "Verify:\n";
    $tables = $db->fetchAll("SHOW TABLES LIKE 'inventory_reports'");
    print_r($tables);
    $desc = $db->getTableSchema('inventory_reports', ['created_at']);
    echo "Columns: " . implode(', ', array_column($desc, 'field')) . "\n";
} catch (Exception $e) {
    echo "ERROR: " . $e->getMessage() . "\n";
}

