<?php
/**
 * Store Keeper Database Setup Script
 * Creates all necessary tables for the Store Keeper module
 */

include 'config.php';

$success = true;
$messages = [];

function runSql($conn, $sql, $okMessage, &$messages, &$success) {
    if ($conn->query($sql) === true) {
        $messages[] = '[OK] ' . $okMessage;
    } else {
        $success = false;
        $messages[] = '[ERR] ' . $okMessage . ': ' . $conn->error;
    }
}

function hasColumn($conn, $table, $column) {
    $table = $conn->real_escape_string($table);
    $column = $conn->real_escape_string($column);
    $result = $conn->query("SHOW COLUMNS FROM `$table` LIKE '$column'");
    return $result && $result->num_rows > 0;
}

function addColumnIfMissing($conn, $table, $column, $definition, &$messages, &$success) {
    if (!hasColumn($conn, $table, $column)) {
        runSql(
            $conn,
            "ALTER TABLE `$table` ADD COLUMN `$column` $definition",
            "Added $table.$column",
            $messages,
            $success
        );
    }
}

// Create store_keepers table
runSql($conn, "CREATE TABLE IF NOT EXISTS store_keepers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "store_keepers table ready", $messages, $success);

// Create stock_receiving table
runSql($conn, "CREATE TABLE IF NOT EXISTS stock_receiving (
    id INT PRIMARY KEY AUTO_INCREMENT,
    store_keeper_id INT,
    ingredient_id INT,
    ingredient_name VARCHAR(255) NOT NULL,
    quantity_received DECIMAL(10, 3) NOT NULL,
    unit VARCHAR(50) NOT NULL,
    supplier_name VARCHAR(100),
    invoice_number VARCHAR(100),
    received_date DATE NOT NULL,
    received_time TIME NOT NULL,
    batch_number VARCHAR(100),
    expiry_date DATE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sk (store_keeper_id),
    INDEX idx_ingredient (ingredient_id),
    INDEX idx_received_date (received_date),
    FOREIGN KEY (store_keeper_id) REFERENCES store_keepers(id) ON DELETE SET NULL,
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "stock_receiving table ready", $messages, $success);

// Create inventory_updates table
runSql($conn, "CREATE TABLE IF NOT EXISTS inventory_updates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    store_keeper_id INT,
    ingredient_id INT,
    ingredient_name VARCHAR(255) NOT NULL,
    action VARCHAR(50) NOT NULL COMMENT 'add, subtract, adjust, transfer',
    quantity_changed DECIMAL(10, 3) NOT NULL,
    unit VARCHAR(50) NOT NULL,
    previous_stock DECIMAL(10, 3),
    new_stock DECIMAL(10, 3),
    reason VARCHAR(255),
    notes TEXT,
    reference_doc VARCHAR(100) COMMENT 'PO, Invoice, Order ID, etc.',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sk (store_keeper_id),
    INDEX idx_ingredient (ingredient_id),
    INDEX idx_action (action),
    INDEX idx_date (created_at),
    FOREIGN KEY (store_keeper_id) REFERENCES store_keepers(id) ON DELETE SET NULL,
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "inventory_updates table ready", $messages, $success);

// Create expiry_wastage_tracking table
runSql($conn, "CREATE TABLE IF NOT EXISTS expiry_wastage_tracking (
    id INT PRIMARY KEY AUTO_INCREMENT,
    store_keeper_id INT,
    ingredient_id INT,
    ingredient_name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL COMMENT 'expiry, wastage, spoilage, recall',
    quantity DECIMAL(10, 3) NOT NULL,
    unit VARCHAR(50) NOT NULL,
    expiry_date DATE COMMENT 'For expiry tracking',
    wastage_date DATE NOT NULL,
    reason VARCHAR(255) NOT NULL,
    reason_details TEXT,
    disposal_method VARCHAR(100),
    status VARCHAR(50) DEFAULT 'documented' COMMENT 'documented, verified, disposed',
    cost_impact DECIMAL(10, 2) COMMENT 'Financial loss',
    notes TEXT,
    batch_number VARCHAR(100),
    photo_ref VARCHAR(255) COMMENT 'Reference to photo/evidence',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    verified_by INT COMMENT 'ID of verifying manager',
    verified_at TIMESTAMP NULL,
    INDEX idx_sk (store_keeper_id),
    INDEX idx_ingredient (ingredient_id),
    INDEX idx_type (type),
    INDEX idx_wastage_date (wastage_date),
    INDEX idx_expiry_date (expiry_date),
    INDEX idx_status (status),
    FOREIGN KEY (store_keeper_id) REFERENCES store_keepers(id) ON DELETE SET NULL,
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "expiry_wastage_tracking table ready", $messages, $success);

// Create inventory_alerts table
runSql($conn, "CREATE TABLE IF NOT EXISTS inventory_alerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    store_keeper_id INT,
    ingredient_id INT,
    alert_type VARCHAR(50) NOT NULL COMMENT 'low_stock, expiry_soon, overstock, missing_inventory',
    ingredient_name VARCHAR(255) NOT NULL,
    current_stock DECIMAL(10, 3),
    threshold_value DECIMAL(10, 3),
    unit VARCHAR(50),
    expiry_date DATE COMMENT 'For expiry alerts',
    days_to_expiry INT COMMENT 'Number of days until expiry',
    severity VARCHAR(50) DEFAULT 'medium' COMMENT 'low, medium, high, critical',
    status VARCHAR(50) DEFAULT 'active' COMMENT 'active, acknowledged, resolved',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    acknowledged_by INT,
    acknowledged_at TIMESTAMP NULL,
    resolved_at TIMESTAMP NULL,
    INDEX idx_sk (store_keeper_id),
    INDEX idx_ingredient (ingredient_id),
    INDEX idx_alert_type (alert_type),
    INDEX idx_status (status),
    INDEX idx_severity (severity),
    FOREIGN KEY (store_keeper_id) REFERENCES store_keepers(id) ON DELETE SET NULL,
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "inventory_alerts table ready", $messages, $success);

// Add necessary columns to ingredients table if missing
addColumnIfMissing($conn, 'ingredients', 'expiry_date', 'DATE NULL COMMENT "Batch expiry date"', $messages, $success);
addColumnIfMissing($conn, 'ingredients', 'batch_number', 'VARCHAR(100) NULL COMMENT "Batch/Lot number"', $messages, $success);
addColumnIfMissing($conn, 'ingredients', 'last_received_date', 'DATE NULL', $messages, $success);
addColumnIfMissing($conn, 'ingredients', 'shelf_location', 'VARCHAR(100) NULL COMMENT "Storage location/bin"', $messages, $success);

?>
<!DOCTYPE html>
<html>
<head>
    <title>Store Keeper Database Setup</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 40px;
            background: #f5f5f5;
        }
        .container {
            background: white;
            padding: 30px;
            border-radius: 8px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            max-width: 800px;
            margin: 0 auto;
        }
        h1 {
            color: #333;
            border-bottom: 2px solid #667eea;
            padding-bottom: 10px;
        }
        h2 {
            color: #667eea;
            margin-top: 25px;
            font-size: 18px;
        }
        .message {
            padding: 8px 12px;
            margin: 8px 0;
            border-radius: 4px;
            font-family: monospace;
            font-size: 13px;
        }
        .message.success {
            background: #d4edda;
            color: #155724;
            border: 1px solid #c3e6cb;
        }
        .message.error {
            background: #f8d7da;
            color: #721c24;
            border: 1px solid #f5c6cb;
        }
        .status {
            margin-top: 20px;
            padding: 15px;
            border-radius: 4px;
            font-weight: bold;
        }
        .status.success {
            background: #d4edda;
            color: #155724;
            border: 1px solid #c3e6cb;
        }
        .status.error {
            background: #f8d7da;
            color: #721c24;
            border: 1px solid #f5c6cb;
        }
    </style>
</head>
<body>
<div class="container">
    <h1>🏪 Store Keeper Module - Database Setup</h1>
    
    <h2>Creating Tables...</h2>
    <?php foreach ($messages as $msg): ?>
        <div class="message <?php echo strpos($msg, '[OK]') === 0 ? 'success' : 'error'; ?>">
            <?php echo htmlspecialchars($msg); ?>
        </div>
    <?php endforeach; ?>
    
    <div class="status <?php echo $success ? 'success' : 'error'; ?>">
        <?php echo $success ? '✅ All tables created successfully!' : '❌ Some errors occurred. Check messages above.'; ?>
    </div>
    
    <h2>Created Tables:</h2>
    <ul>
        <li><strong>store_keepers</strong> - Store keeper user accounts</li>
        <li><strong>stock_receiving</strong> - Log of received stock items</li>
        <li><strong>inventory_updates</strong> - Track all inventory changes</li>
        <li><strong>expiry_wastage_tracking</strong> - Document expiry and wastage</li>
        <li><strong>inventory_alerts</strong> - Automated alerts for stock issues</li>
    </ul>
    
    <h2>Enhanced Ingredients Table:</h2>
    <ul>
        <li>expiry_date - Batch expiry tracking</li>
        <li>batch_number - Lot/Batch identification</li>
        <li>last_received_date - Track receipt date</li>
        <li>shelf_location - Storage location reference</li>
    </ul>
</div>
</body>
</html>
