CREATE DATABASE IF NOT EXISTS delta_ops_portal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE delta_ops_portal;
DROP TABLE IF EXISTS audit_logs,approvals,tickets,leave_requests,receiving_lines,receivings,order_lines,supplier_orders,performance_deductions,price_tag_audits,incidents,daily_photos,users,departments,branches,suppliers;
CREATE TABLE branches(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,location VARCHAR(120),active TINYINT DEFAULT 1);
CREATE TABLE departments(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,active TINYINT DEFAULT 1);
CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,branch_id INT NULL,department_id INT NULL,manager_id INT NULL,name VARCHAR(120) NOT NULL,email VARCHAR(150) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,role VARCHAR(40) NOT NULL,job_title VARCHAR(120),active TINYINT DEFAULT 1,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(branch_id) REFERENCES branches(id),FOREIGN KEY(department_id) REFERENCES departments(id));
CREATE TABLE suppliers(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(160) NOT NULL,phone VARCHAR(60),email VARCHAR(120),active TINYINT DEFAULT 1);
CREATE TABLE daily_photos(id INT AUTO_INCREMENT PRIMARY KEY,branch_id INT,department_id INT,line_section VARCHAR(120),report_type VARCHAR(80),status VARCHAR(30),comment TEXT,photo_path VARCHAR(255),submitted_by INT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE incidents(id INT AUTO_INCREMENT PRIMARY KEY,incident_no VARCHAR(30) UNIQUE,branch_id INT,department_id INT,type VARCHAR(80),category VARCHAR(120),title VARCHAR(180),description TEXT,product_sku VARCHAR(80),batch_no VARCHAR(80),qty_affected DECIMAL(12,2) DEFAULT 0,evidence_path VARCHAR(255),responsible_user_id INT NULL,reported_by INT,verified_by INT NULL,root_cause TEXT,corrective_action TEXT,status VARCHAR(30) DEFAULT 'OPEN',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,closed_at DATETIME NULL);
CREATE TABLE price_tag_audits(id INT AUTO_INCREMENT PRIMARY KEY,branch_id INT,department_id INT,item_code VARCHAR(80),item_name VARCHAR(180),rms_price DECIMAL(12,2),shelf_price DECIMAL(12,2),result VARCHAR(20),tag_photo VARCHAR(255),after_photo VARCHAR(255),incident_id INT NULL,submitted_by INT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE performance_deductions(id INT AUTO_INCREMENT PRIMARY KEY,period_ym CHAR(7) NOT NULL,user_id INT NOT NULL,incident_id INT NULL,category VARCHAR(120),reason TEXT,points_deducted INT DEFAULT 1,reported_by INT,verified_by INT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,CONSTRAINT chk_one_point CHECK(points_deducted=1));
CREATE TABLE supplier_orders(id INT AUTO_INCREMENT PRIMARY KEY,po_no VARCHAR(30) UNIQUE,supplier_id INT,branch_id INT,requested_by INT,status VARCHAR(30) DEFAULT 'DRAFT',notes TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE order_lines(id INT AUTO_INCREMENT PRIMARY KEY,order_id INT,item_code VARCHAR(80),description VARCHAR(180),qty DECIMAL(12,2),unit_cost DECIMAL(12,2));
CREATE TABLE receivings(id INT AUTO_INCREMENT PRIMARY KEY,grn_no VARCHAR(30) UNIQUE,order_id INT,branch_id INT,invoice_no VARCHAR(80),invoice_date DATE,invoice_amount DECIMAL(12,2),vat_amount DECIMAL(12,2),invoice_file VARCHAR(255),delivery_note_file VARCHAR(255),received_by INT,verified_by INT NULL,status VARCHAR(30) DEFAULT 'SUBMITTED',created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE receiving_lines(id INT AUTO_INCREMENT PRIMARY KEY,receiving_id INT,item_code VARCHAR(80),description VARCHAR(180),ordered_qty DECIMAL(12,2),received_qty DECIMAL(12,2),damaged_qty DECIMAL(12,2),short_qty DECIMAL(12,2),batch_no VARCHAR(80),expiry_date DATE NULL,remarks TEXT);
CREATE TABLE approvals(id INT AUTO_INCREMENT PRIMARY KEY,module VARCHAR(60),ref_id INT,requested_by INT,approved_by INT NULL,status VARCHAR(30) DEFAULT 'PENDING',comment TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,decided_at DATETIME NULL);
CREATE TABLE leave_requests(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT,from_date DATE,to_date DATE,reason TEXT,status VARCHAR(30) DEFAULT 'PENDING',approved_by INT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE tickets(id INT AUTO_INCREMENT PRIMARY KEY,branch_id INT,department_id INT,category VARCHAR(80),title VARCHAR(180),description TEXT,assigned_to INT NULL,status VARCHAR(30) DEFAULT 'OPEN',created_by INT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE audit_logs(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT,action VARCHAR(80),ref VARCHAR(120),details TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
INSERT INTO branches(name,location) VALUES('Nandi Hills Main','Nandi Hills'),('Kapsabet NAMGOI','Kapsabet'),('Maraba','Maraba'),('Eldoret Main','Eldoret');
INSERT INTO departments(name) VALUES('Supermarket Floor'),('Bakery'),('Field Sales'),('Receiving'),('Storekeeping'),('Dispatch'),('Cashiers / Front End'),('Accounts'),('HR'),('Management');
INSERT INTO suppliers(name,phone) VALUES('Sample Supplier','0700000000');
INSERT INTO users(branch_id,department_id,name,email,password_hash,role,job_title) VALUES
(NULL,10,'System Director','admin@delta.local', '$2y$12$FYqeDt93D2R9ZWSDS.XaV.oOztFFdIDDP19ccoDXmREbsJ7EpM.gu','DIRECTOR','Director'),
(1,10,'Branch Manager','manager@delta.local', '$2y$12$FYqeDt93D2R9ZWSDS.XaV.oOztFFdIDDP19ccoDXmREbsJ7EpM.gu','BRANCH_MANAGER','Branch Manager'),
(1,2,'Bakery Line Manager','bakery@delta.local', '$2y$12$FYqeDt93D2R9ZWSDS.XaV.oOztFFdIDDP19ccoDXmREbsJ7EpM.gu','LINE_MANAGER','Bakery Line Manager'),
(1,7,'Sample Cashier','cashier@delta.local', '$2y$12$FYqeDt93D2R9ZWSDS.XaV.oOztFFdIDDP19ccoDXmREbsJ7EpM.gu','CASHIER','Cashier');