CREATE DATABASE IF NOT EXISTS lo_tracking CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE lo_tracking;

CREATE TABLE roles (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE users (
 id INT AUTO_INCREMENT PRIMARY KEY,
 role_id INT NOT NULL,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE branches (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL UNIQUE,
 active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE vendors (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL UNIQUE,
 contact_person VARCHAR(120) NULL,
 phone VARCHAR(80) NULL,
 email VARCHAR(190) NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers (
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 ic_number VARCHAR(50) NULL,
 address1 VARCHAR(255) NULL,
 address2 VARCHAR(255) NULL,
 address3 VARCHAR(255) NULL,
 postcode VARCHAR(20) NULL,
 city VARCHAR(100) NULL,
 phone VARCHAR(100) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE items (
 id INT AUTO_INCREMENT PRIMARY KEY,
 item_code VARCHAR(80) NULL UNIQUE,
 category VARCHAR(120) NULL,
 name VARCHAR(255) NOT NULL,
 uom VARCHAR(50) NULL,
 cost DECIMAL(15,4) NOT NULL DEFAULT 0,
 supplier_name VARCHAR(190) NULL,
 reorder_level DECIMAL(15,3) NOT NULL DEFAULT 0,
 reorder_qty DECIMAL(15,3) NOT NULL DEFAULT 0,
 controlled_item TINYINT(1) NOT NULL DEFAULT 0,
 remark TEXT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(category), INDEX(name)
);

CREATE TABLE los (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 lo_number VARCHAR(100) NOT NULL UNIQUE,
 lo_date DATE NULL,
 date_received DATE NULL,
 submitted_date DATE NULL,
 branch_id INT NULL,
 amount DECIMAL(15,2) NOT NULL DEFAULT 0,
 cost_amount DECIMAL(15,2) NULL,
 quotation_number VARCHAR(100) NULL,
 beneficiary_name VARCHAR(190) NULL,
 customer_id INT NULL,
 due_date DATE NULL,
 status ENUM('Draft','Active','Partially Fulfilled','Fulfilled','Expired','Cancelled') NOT NULL DEFAULT 'Active',
 priority ENUM('Normal','Urgent','Critical') NOT NULL DEFAULT 'Normal',
 remarks TEXT NULL,
 created_by INT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(branch_id) REFERENCES branches(id) ON DELETE SET NULL,
 FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(due_date), INDEX(status), INDEX(branch_id)
);

CREATE TABLE lo_items (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 lo_id BIGINT NOT NULL,
 item_id INT NULL,
 item_description VARCHAR(255) NOT NULL,
 qty DECIMAL(15,3) NOT NULL DEFAULT 1,
 uom VARCHAR(50) NULL,
 estimated_unit_cost DECIMAL(15,4) NOT NULL DEFAULT 0,
 remarks TEXT NULL,
 FOREIGN KEY(lo_id) REFERENCES los(id) ON DELETE CASCADE,
 FOREIGN KEY(item_id) REFERENCES items(id) ON DELETE SET NULL,
 INDEX(lo_id)
);

CREATE TABLE purchase_orders (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 lo_id BIGINT NOT NULL,
 vendor_id INT NULL,
 po_number VARCHAR(100) NOT NULL UNIQUE,
 po_date DATE NULL,
 submitted_date DATE NULL,
 expected_delivery_date DATE NULL,
 status ENUM('Draft','Issued','Partially Received','Fully Received','Cancelled','Closed') NOT NULL DEFAULT 'Draft',
 amount DECIMAL(15,2) NOT NULL DEFAULT 0,
 legacy_do_number VARCHAR(100) NULL,
 invoice_number VARCHAR(100) NULL,
 invoice_date DATE NULL,
 notes TEXT NULL,
 created_by INT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(lo_id) REFERENCES los(id) ON DELETE CASCADE,
 FOREIGN KEY(vendor_id) REFERENCES vendors(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(lo_id), INDEX(vendor_id), INDEX(expected_delivery_date), INDEX(status)
);

CREATE TABLE po_items (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 po_id BIGINT NOT NULL,
 lo_item_id BIGINT NULL,
 item_id INT NULL,
 description VARCHAR(255) NOT NULL,
 ordered_qty DECIMAL(15,3) NOT NULL DEFAULT 0,
 unit_cost DECIMAL(15,4) NOT NULL DEFAULT 0,
 received_qty DECIMAL(15,3) NOT NULL DEFAULT 0,
 outstanding_qty DECIMAL(15,3) GENERATED ALWAYS AS (GREATEST(ordered_qty-received_qty,0)) STORED,
 status ENUM('Pending','Partially Received','Fully Received','Cancelled') NOT NULL DEFAULT 'Pending',
 remarks TEXT NULL,
 FOREIGN KEY(po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
 FOREIGN KEY(lo_item_id) REFERENCES lo_items(id) ON DELETE SET NULL,
 FOREIGN KEY(item_id) REFERENCES items(id) ON DELETE SET NULL,
 INDEX(po_id), INDEX(item_id), INDEX(status)
);

CREATE TABLE deliveries (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 po_id BIGINT NOT NULL,
 grn_number VARCHAR(100) NULL UNIQUE,
 delivery_date DATE NOT NULL,
 received_by INT NULL,
 do_number VARCHAR(100) NULL,
 condition_status ENUM('Good','Damaged','Partial','Rejected') NOT NULL DEFAULT 'Good',
 remarks TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
 FOREIGN KEY(received_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(po_id), INDEX(delivery_date)
);

CREATE TABLE delivery_items (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 delivery_id BIGINT NOT NULL,
 po_item_id BIGINT NOT NULL,
 qty_received DECIMAL(15,3) NOT NULL DEFAULT 0,
 unit_cost DECIMAL(15,4) NOT NULL DEFAULT 0,
 expiry_date DATE NULL,
 batch_no VARCHAR(100) NULL,
 remarks TEXT NULL,
 FOREIGN KEY(delivery_id) REFERENCES deliveries(id) ON DELETE CASCADE,
 FOREIGN KEY(po_item_id) REFERENCES po_items(id) ON DELETE CASCADE
);

CREATE TABLE stock_movements (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 item_id INT NOT NULL,
 movement_type ENUM('IN','OUT','ADJUSTMENT') NOT NULL,
 quantity DECIMAL(15,3) NOT NULL,
 movement_date DATE NOT NULL,
 reference_type VARCHAR(40) NULL,
 reference_id BIGINT NULL,
 reference_no VARCHAR(100) NULL,
 unit_cost DECIMAL(15,4) NOT NULL DEFAULT 0,
 remarks TEXT NULL,
 created_by INT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(item_id) REFERENCES items(id) ON DELETE RESTRICT,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(item_id), INDEX(movement_date), INDEX(reference_type,reference_id)
);

CREATE TABLE alerts (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 alert_type VARCHAR(60) NOT NULL,
 severity ENUM('INFO','WARNING','CRITICAL') NOT NULL DEFAULT 'WARNING',
 title VARCHAR(255) NOT NULL,
 message TEXT NOT NULL,
 reference_type VARCHAR(40) NULL,
 reference_id BIGINT NULL,
 due_date DATE NULL,
 is_read TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_alert(alert_type,reference_type,reference_id,due_date)
);

CREATE TABLE audit_logs (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 user_id INT NULL,
 action VARCHAR(40) NOT NULL,
 table_name VARCHAR(80) NULL,
 record_id BIGINT NULL,
 old_values JSON NULL,
 new_values JSON NULL,
 ip_address VARCHAR(45) NULL,
 user_agent VARCHAR(255) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX(user_id), INDEX(table_name,record_id), INDEX(created_at)
);

CREATE TABLE settings (
 setting_key VARCHAR(100) PRIMARY KEY,
 setting_value TEXT NULL
);

INSERT INTO roles(name) VALUES ('Admin'),('Procurement'),('Store/Warehouse'),('Management'),('Viewer')
ON DUPLICATE KEY UPDATE name=VALUES(name);

INSERT INTO settings VALUES
('lo_due_warning_days','7'),
('po_due_warning_days','3'),
('app_name','LO / PO / Delivery Tracking System')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);

CREATE OR REPLACE VIEW v_po_item_status AS
SELECT p.id po_id,p.po_number,p.lo_id,l.lo_number,p.vendor_id,v.name vendor_name,
       pi.id po_item_id,pi.description,pi.ordered_qty,pi.received_qty,
       GREATEST(pi.ordered_qty-pi.received_qty,0) outstanding_qty,pi.status,
       p.expected_delivery_date,p.status po_status
FROM purchase_orders p
JOIN los l ON l.id=p.lo_id
LEFT JOIN vendors v ON v.id=p.vendor_id
JOIN po_items pi ON pi.po_id=p.id;

CREATE OR REPLACE VIEW v_current_stock AS
SELECT i.id,i.item_code,i.name,i.category,i.uom,i.cost,i.reorder_level,
       COALESCE(SUM(CASE WHEN sm.movement_type='IN' THEN sm.quantity
                         WHEN sm.movement_type='OUT' THEN -sm.quantity
                         ELSE sm.quantity END),0) stock_balance
FROM items i
LEFT JOIN stock_movements sm ON sm.item_id=i.id
GROUP BY i.id,i.item_code,i.name,i.category,i.uom,i.cost,i.reorder_level;