-- Import into a database already created in cPanel. No CREATE DATABASE/USE.
CREATE TABLE IF NOT EXISTS settings (`key` VARCHAR(80) PRIMARY KEY,`value` TEXT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL,username VARCHAR(80) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('superadmin','manager','kasir','produksi') NOT NULL DEFAULT 'kasir',active TINYINT NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS products (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,sku VARCHAR(50) NULL UNIQUE,price DECIMAL(14,2) NOT NULL DEFAULT 0,cost DECIMAL(14,2) NOT NULL DEFAULT 0,active TINYINT NOT NULL DEFAULT 1) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS materials (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,unit VARCHAR(30) NOT NULL DEFAULT 'pcs',stock DECIMAL(14,3) NOT NULL DEFAULT 0,min_stock DECIMAL(14,3) NOT NULL DEFAULT 0,cost DECIMAL(14,2) NOT NULL DEFAULT 0) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS recipes (product_id INT NOT NULL,material_id INT NOT NULL,qty DECIMAL(14,3) NOT NULL,PRIMARY KEY(product_id,material_id),FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(material_id) REFERENCES materials(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS finished_stock (product_id INT PRIMARY KEY,qty INT NOT NULL DEFAULT 0,FOREIGN KEY(product_id) REFERENCES products(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS production (id INT AUTO_INCREMENT PRIMARY KEY,product_id INT NOT NULL,qty INT NOT NULL,produced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,user_id INT NOT NULL,FOREIGN KEY(product_id) REFERENCES products(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS stock_movements (id INT AUTO_INCREMENT PRIMARY KEY,material_id INT NOT NULL,qty_change DECIMAL(14,3) NOT NULL,note VARCHAR(255),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,user_id INT NOT NULL,FOREIGN KEY(material_id) REFERENCES materials(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sales (id INT AUTO_INCREMENT PRIMARY KEY,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,method ENUM('cash','qris','mixed') NOT NULL,cash_amount DECIMAL(14,2) NOT NULL DEFAULT 0,qris_amount DECIMAL(14,2) NOT NULL DEFAULT 0,total DECIMAL(14,2) NOT NULL,user_id INT NOT NULL,note VARCHAR(255),FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS sale_items (id INT AUTO_INCREMENT PRIMARY KEY,sale_id INT NOT NULL,product_id INT NOT NULL,qty INT NOT NULL,unit_price DECIMAL(14,2) NOT NULL,unit_cost DECIMAL(14,2) NOT NULL,FOREIGN KEY(sale_id) REFERENCES sales(id),FOREIGN KEY(product_id) REFERENCES products(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS suppliers (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,contact VARCHAR(150)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS purchase_orders (id INT AUTO_INCREMENT PRIMARY KEY,supplier_id INT NULL,material_id INT NOT NULL,qty DECIMAL(14,3) NOT NULL,unit_price DECIMAL(14,2) NOT NULL DEFAULT 0,link VARCHAR(500),status ENUM('draft','ordered','received','cancelled') NOT NULL DEFAULT 'draft',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,received_at DATETIME NULL,FOREIGN KEY(material_id) REFERENCES materials(id),FOREIGN KEY(supplier_id) REFERENCES suppliers(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS expenses (id INT AUTO_INCREMENT PRIMARY KEY,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,category VARCHAR(100) NOT NULL,amount DECIMAL(14,2) NOT NULL,method ENUM('cash','qris','transfer') NOT NULL DEFAULT 'cash',note VARCHAR(255),user_id INT NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS cash_closings (id INT AUTO_INCREMENT PRIMARY KEY,closing_date DATE NOT NULL UNIQUE,opening_cash DECIMAL(14,2) NOT NULL DEFAULT 0,actual_cash DECIMAL(14,2) NOT NULL DEFAULT 0,expected_cash DECIMAL(14,2) NOT NULL DEFAULT 0,difference DECIMAL(14,2) NOT NULL DEFAULT 0,note VARCHAR(255),user_id INT NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_logs (id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,action VARCHAR(120) NOT NULL,detail TEXT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO products(name,sku,price,cost) VALUES ('Gemoy Coin Original','ORIGINAL',0,0),('Gemoy Coin Cokelat','COKELAT',0,0),('Gemoy Coin Keju','KEJU',0,0);

CREATE TABLE IF NOT EXISTS staff (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,position VARCHAR(100),phone VARCHAR(50),active TINYINT NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS assets (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,category VARCHAR(100),qty INT NOT NULL DEFAULT 1,acquisition_value DECIMAL(14,2) NOT NULL DEFAULT 0,condition_note VARCHAR(255),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS feedback (id INT AUTO_INCREMENT PRIMARY KEY,customer_name VARCHAR(150),category VARCHAR(50),rating TINYINT NOT NULL,comment TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;