CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120),email VARCHAR(160) UNIQUE,password_hash VARCHAR(255),role VARCHAR(40),is_active TINYINT DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO users(name,email,password_hash,role) VALUES('Super Admin','admin@pet.local','$2y$10$7Fb/HyQdy3mBJVYz.kEmt.A6nRA76V3aHlIk6FbXtlOy/4DYrF4Dq','SUPER_ADMIN');
CREATE TABLE customers(id INT AUTO_INCREMENT PRIMARY KEY,customer_code VARCHAR(50),customer_name VARCHAR(180),phone VARCHAR(50),email VARCHAR(160),credit_limit DECIMAL(14,2) DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT INTO customers(customer_code,customer_name,phone,credit_limit) VALUES('CUST-001','Walk In Customer','',0),('CUST-002','Credit Customer Ltd','0700000000',500000);
CREATE TABLE products(id INT AUTO_INCREMENT PRIMARY KEY,product_code VARCHAR(50),product_name VARCHAR(180),category ENUM('PREFORM','BOTTLE','CAP','RAW_MATERIAL') DEFAULT 'BOTTLE',weight_grams DECIMAL(10,3) DEFAULT 0,pack_size INT DEFAULT 1,selling_price DECIMAL(14,2) DEFAULT 0,is_active TINYINT DEFAULT 1);
INSERT INTO products(product_code,product_name,category,weight_grams,pack_size,selling_price) VALUES('PET500-PF','500ml Preform','PREFORM',18.5,1000,4.5),('PET500-BTL','500ml Bottle','BOTTLE',18.5,24,8.5),('PET1L-BTL','1L Bottle','BOTTLE',28,12,14),('RESIN-PET','PET Virgin Resin Kg','RAW_MATERIAL',0,1,0),('MB-BLUE','Blue Masterbatch Kg','RAW_MATERIAL',0,1,0);
CREATE TABLE machines(id INT AUTO_INCREMENT PRIMARY KEY,machine_code VARCHAR(50),machine_name VARCHAR(160),process_type ENUM('INJECTION','BLOW'),capacity_per_hour INT DEFAULT 0,status ENUM('RUNNING','STOPPED','MOLD_CHANGE','MAINTENANCE') DEFAULT 'STOPPED');
INSERT INTO machines(machine_code,machine_name,process_type,capacity_per_hour,status) VALUES('INJ-01','Injection Molding 01','INJECTION',50000,'STOPPED'),('BLOW-01','Stretch Blow 01','BLOW',45000,'STOPPED');
CREATE TABLE molds(id INT AUTO_INCREMENT PRIMARY KEY,mold_code VARCHAR(50),mold_name VARCHAR(160),cavity_count INT DEFAULT 0,status VARCHAR(30) DEFAULT 'ACTIVE');
INSERT INTO molds(mold_code,mold_name,cavity_count) VALUES('MOLD-500ML','500ml Bottle Mold',8),('MOLD-PF-500','500ml Preform Mold',48);
CREATE TABLE production_runs(id INT AUTO_INCREMENT PRIMARY KEY,run_date DATE,shift ENUM('DAY','NIGHT'),machine_id INT,product_id INT,mold_id INT,operator_id INT,start_time DATETIME,end_time DATETIME NULL,status ENUM('RUNNING','DONE','STOPPED') DEFAULT 'RUNNING');
CREATE TABLE production_entries(id INT AUTO_INCREMENT PRIMARY KEY,run_id INT,shots_start INT DEFAULT 0,shots_end INT DEFAULT 0,good_qty DECIMAL(14,2) DEFAULT 0,reject_qty DECIMAL(14,2) DEFAULT 0,rejected_preforms DECIMAL(14,2) DEFAULT 0,preforms_consumed DECIMAL(14,2) DEFAULT 0,virgin_kg DECIMAL(14,2) DEFAULT 0,masterbatch_kg DECIMAL(14,2) DEFAULT 0,packed_bags DECIMAL(14,2) DEFAULT 0,remarks TEXT,created_by INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE production_notes(id INT AUTO_INCREMENT PRIMARY KEY,note_type VARCHAR(50),machine_id INT,shift VARCHAR(20),note TEXT,created_by INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE stores_receipts(id INT AUTO_INCREMENT PRIMARY KEY,run_id INT UNIQUE,received_qty DECIMAL(14,2),received_by INT,received_at DATETIME,remarks TEXT);
CREATE TABLE stock_movements(id INT AUTO_INCREMENT PRIMARY KEY,item_type ENUM('RAW','FINISHED'),item_id INT,movement_date DATE,source VARCHAR(80),qty_in DECIMAL(14,2) DEFAULT 0,qty_out DECIMAL(14,2) DEFAULT 0,reference VARCHAR(120),created_by INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE sales_orders(id INT AUTO_INCREMENT PRIMARY KEY,order_no VARCHAR(60),order_date DATE,customer_id INT,order_type ENUM('WALK_IN','FILL_TRUCK','CREDIT'),status VARCHAR(30) DEFAULT 'OPEN',total_amount DECIMAL(14,2) DEFAULT 0,created_by INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE sales_order_lines(id INT AUTO_INCREMENT PRIMARY KEY,sales_order_id INT,product_id INT,qty DECIMAL(14,2),unit_price DECIMAL(14,2),line_total DECIMAL(14,2));
CREATE TABLE dispatches(id INT AUTO_INCREMENT PRIMARY KEY,dispatch_no VARCHAR(60),dispatch_date DATE,sales_order_id INT,truck_no VARCHAR(80),driver_name VARCHAR(120),loaded_qty DECIMAL(14,2),loaded_weight DECIMAL(14,2),status VARCHAR(30),created_by INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE invoices(id INT AUTO_INCREMENT PRIMARY KEY,invoice_no VARCHAR(60),invoice_date DATE,customer_id INT,sales_order_id INT,total_amount DECIMAL(14,2),status VARCHAR(30) DEFAULT 'UNPAID',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE payments(id INT AUTO_INCREMENT PRIMARY KEY,receipt_no VARCHAR(60),payment_date DATE,customer_id INT,amount DECIMAL(14,2),method VARCHAR(40),reference_no VARCHAR(100),created_by INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE ncr_tickets(id INT AUTO_INCREMENT PRIMARY KEY,ticket_no VARCHAR(60),ticket_date DATE,source VARCHAR(40),customer_id INT NULL,product_id INT NULL,issue TEXT,corrective_action TEXT,status VARCHAR(30),created_by INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);