CREATE TABLE IF NOT EXISTS inventory_items (item_id INT AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(50) UNIQUE, item_name VARCHAR(150) NOT NULL, category ENUM('casket','urn','vault','supply','vehicle','other') DEFAULT 'other', description TEXT, serial_required TINYINT(1) DEFAULT 0, default_cost DECIMAL(10,2), default_price DECIMAL(10,2), active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS inventory_serials (serial_id INT AUTO_INCREMENT PRIMARY KEY, item_id INT NOT NULL, serial_number VARCHAR(100) UNIQUE, branch_id INT NOT NULL, status ENUM('in_stock','assigned','used','returned','transferred') DEFAULT 'in_stock', assigned_case INT DEFAULT NULL);
CREATE TABLE IF NOT EXISTS inventory_stock (stock_id INT AUTO_INCREMENT PRIMARY KEY, branch_id INT NOT NULL, item_id INT NOT NULL, quantity INT DEFAULT 0, reorder_level INT DEFAULT 0);
CREATE TABLE IF NOT EXISTS purchase_orders (po_id INT AUTO_INCREMENT PRIMARY KEY, branch_id INT NOT NULL, vendor_name VARCHAR(150), order_date DATETIME DEFAULT CURRENT_TIMESTAMP, expected_date DATETIME DEFAULT NULL, status ENUM('pending','ordered','received','cancelled') DEFAULT 'pending', notes TEXT);
CREATE TABLE IF NOT EXISTS purchase_order_items (poi_id INT AUTO_INCREMENT PRIMARY KEY, po_id INT NOT NULL, item_id INT NOT NULL, quantity INT DEFAULT 1, unit_cost DECIMAL(10,2));
CREATE TABLE IF NOT EXISTS case_inventory (case_inv_id INT AUTO_INCREMENT PRIMARY KEY, case_id INT NOT NULL, item_id INT NOT NULL, serial_id INT DEFAULT NULL, assigned_by INT, assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
