<?php
/**
 * Purchase Manager Database Setup + Migration Script
 */

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
        );
    }
}

runSql($conn, "CREATE TABLE IF NOT EXISTS purchase_managers (
    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", "purchase_managers table ready", $messages, $success);

runSql($conn, "CREATE TABLE IF NOT EXISTS suppliers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    purchase_manager_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    contact_person VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    rating DECIMAL(3, 2),
    lead_time_days INT DEFAULT 0,
    payment_terms VARCHAR(100),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pm (purchase_manager_id),
    INDEX idx_supplier_status (status),
    FOREIGN KEY (purchase_manager_id) REFERENCES purchase_managers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "suppliers table ready", $messages, $success);

runSql($conn, "CREATE TABLE IF NOT EXISTS purchase_requisitions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    purchase_manager_id INT NOT NULL,
    ingredient_id INT NULL,
    item_description VARCHAR(255) NOT NULL,
    quantity DECIMAL(10, 3) NOT NULL,
    unit VARCHAR(50),
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    requested_by_role VARCHAR(50) DEFAULT 'kitchen',
    requested_by_name VARCHAR(100) NULL,
    needed_by_date DATE NULL,
    status ENUM('pending', 'approved', 'rejected', 'ordered', 'closed') DEFAULT 'pending',
    approval_date TIMESTAMP NULL,
    approved_by_manager_id INT NULL,
    rejection_reason VARCHAR(255) NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_req_pm (purchase_manager_id),
    INDEX idx_req_status (status),
    INDEX idx_req_priority (priority),
    INDEX idx_req_ingredient (ingredient_id),
    FOREIGN KEY (purchase_manager_id) REFERENCES purchase_managers(id) ON DELETE CASCADE,
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "purchase_requisitions table ready", $messages, $success);

runSql($conn, "CREATE TABLE IF NOT EXISTS purchase_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    purchase_manager_id INT NOT NULL,
    requisition_id INT NULL,
    supplier_id INT NULL,
    ingredient_id INT NULL,
    po_number VARCHAR(50) NULL,
    item_name VARCHAR(255) NOT NULL,
    quantity DECIMAL(10, 3) NOT NULL,
    unit VARCHAR(50) NULL,
    unit_price DECIMAL(10, 2) DEFAULT 0,
    total_cost DECIMAL(12, 2) DEFAULT 0,
    received_quantity DECIMAL(10, 3) DEFAULT 0,
    status ENUM('draft', 'confirmed', 'shipped', 'partially_received', 'delivered', 'cancelled') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expected_delivery_date DATE NULL,
    delivery_date TIMESTAMP NULL,
    invoice_number VARCHAR(100) NULL,
    received_by VARCHAR(100) NULL,
    notes TEXT,
    INDEX idx_po_pm (purchase_manager_id),
    INDEX idx_po_supplier (supplier_id),
    INDEX idx_po_status (status),
    INDEX idx_po_req (requisition_id),
    INDEX idx_po_ingredient (ingredient_id),
    FOREIGN KEY (purchase_manager_id) REFERENCES purchase_managers(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
    FOREIGN KEY (requisition_id) REFERENCES purchase_requisitions(id) ON DELETE SET NULL,
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "purchase_orders table ready", $messages, $success);

runSql($conn, "CREATE TABLE IF NOT EXISTS cost_comparisons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    purchase_manager_id INT NOT NULL,
    ingredient_id INT NULL,
    item_name VARCHAR(255) NOT NULL,
    supplier_id INT,
    price DECIMAL(10, 2) NOT NULL,
    quantity DECIMAL(10, 3) DEFAULT 1,
    unit VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_cc_pm (purchase_manager_id),
    INDEX idx_cc_item (item_name),
    INDEX idx_cc_supplier (supplier_id),
    INDEX idx_cc_ingredient (ingredient_id),
    FOREIGN KEY (purchase_manager_id) REFERENCES purchase_managers(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "cost_comparisons table ready", $messages, $success);

runSql($conn, "CREATE TABLE IF NOT EXISTS supplier_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    supplier_id INT NOT NULL,
    ingredient_id INT NOT NULL,
    supplier_sku VARCHAR(100) NULL COMMENT 'Supplier product code',
    supplier_product_name VARCHAR(255) NULL COMMENT 'Name supplier uses for product',
    unit VARCHAR(50) NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL DEFAULT 0,
    minimum_order_qty DECIMAL(10, 3) DEFAULT 1,
    lead_time_days INT DEFAULT 0,
    availability VARCHAR(50) DEFAULT 'available' COMMENT 'available, discontinued, seasonal',
    is_preferred BOOLEAN DEFAULT FALSE,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_supplier (supplier_id),
    INDEX idx_ingredient (ingredient_id),
    INDEX idx_availability (availability),
    UNIQUE KEY unique_supplier_ingredient (supplier_id, ingredient_id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE,
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", "supplier_products table ready", $messages, $success);

// Lightweight migrations for existing installs
addColumnIfMissing($conn, 'suppliers', 'lead_time_days', "INT DEFAULT 0", $messages, $success);
addColumnIfMissing($conn, 'suppliers', 'payment_terms', "VARCHAR(100) NULL", $messages, $success);
addColumnIfMissing($conn, 'suppliers', 'status', "ENUM('active','inactive') DEFAULT 'active'", $messages, $success);

addColumnIfMissing($conn, 'purchase_requisitions', 'ingredient_id', "INT NULL", $messages, $success);
addColumnIfMissing($conn, 'purchase_requisitions', 'priority', "ENUM('low','medium','high','urgent') DEFAULT 'medium'", $messages, $success);
addColumnIfMissing($conn, 'purchase_requisitions', 'requested_by_role', "VARCHAR(50) DEFAULT 'kitchen'", $messages, $success);
addColumnIfMissing($conn, 'purchase_requisitions', 'requested_by_name', "VARCHAR(100) NULL", $messages, $success);
addColumnIfMissing($conn, 'purchase_requisitions', 'needed_by_date', "DATE NULL", $messages, $success);
addColumnIfMissing($conn, 'purchase_requisitions', 'approved_by_manager_id', "INT NULL", $messages, $success);
addColumnIfMissing($conn, 'purchase_requisitions', 'rejection_reason', "VARCHAR(255) NULL", $messages, $success);

addColumnIfMissing($conn, 'purchase_orders', 'requisition_id', "INT NULL", $messages, $success);
addColumnIfMissing($conn, 'purchase_orders', 'ingredient_id', "INT NULL", $messages, $success);
addColumnIfMissing($conn, 'purchase_orders', 'po_number', "VARCHAR(50) NULL", $messages, $success);
addColumnIfMissing($conn, 'purchase_orders', 'unit', "VARCHAR(50) NULL", $messages, $success);
addColumnIfMissing($conn, 'purchase_orders', 'received_quantity', "DECIMAL(10,3) DEFAULT 0", $messages, $success);
addColumnIfMissing($conn, 'purchase_orders', 'expected_delivery_date', "DATE NULL", $messages, $success);
addColumnIfMissing($conn, 'purchase_orders', 'invoice_number', "VARCHAR(100) NULL", $messages, $success);
addColumnIfMissing($conn, 'purchase_orders', 'received_by', "VARCHAR(100) NULL", $messages, $success);

addColumnIfMissing($conn, 'cost_comparisons', 'ingredient_id', "INT NULL", $messages, $success);

// Ensure decimal quantity types where possible
runSql($conn, "ALTER TABLE purchase_requisitions MODIFY quantity DECIMAL(10, 3) NOT NULL", "purchase_requisitions.quantity normalized", $messages, $success);
runSql($conn, "ALTER TABLE purchase_orders MODIFY quantity DECIMAL(10, 3) NOT NULL", "purchase_orders.quantity normalized", $messages, $success);
runSql($conn, "ALTER TABLE cost_comparisons MODIFY quantity DECIMAL(10, 3) DEFAULT 1", "cost_comparisons.quantity normalized", $messages, $success);

header('Content-Type: application/json');
echo json_encode([
    'success' => $success,
    'message' => $success ? 'Purchase manager setup/migration completed' : 'Setup completed with some errors',
    'details' => $messages
]);
