CREATE DATABASE IF NOT EXISTS toko_online CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE toko_online;
CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(80) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,name VARCHAR(120) NOT NULL,role ENUM('admin','view') NOT NULL DEFAULT 'view',active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE customers(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,address TEXT NOT NULL,phone VARCHAR(40) NOT NULL,email VARCHAR(160) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE products(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(160) NOT NULL,stock INT NOT NULL DEFAULT 0,category VARCHAR(100),size VARCHAR(80),weight DECIMAL(10,2) DEFAULT 0,height DECIMAL(10,2) DEFAULT 0,price DECIMAL(15,2) NOT NULL,code VARCHAR(80) UNIQUE,image VARCHAR(255),active TINYINT(1) DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE sliders(id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(160),subtitle VARCHAR(255),image VARCHAR(255) NOT NULL,sort_order INT DEFAULT 0,active TINYINT(1) DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE shop_settings(id INT AUTO_INCREMENT PRIMARY KEY,shop_name VARCHAR(160),address TEXT,phone VARCHAR(60),email VARCHAR(160),maps_embed TEXT);
CREATE TABLE orders(id INT AUTO_INCREMENT PRIMARY KEY,customer_name VARCHAR(120) NOT NULL,address TEXT NOT NULL,phone VARCHAR(40) NOT NULL,status ENUM('Belum Diproses','Selesai Diproses') DEFAULT 'Belum Diproses',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE order_items(id INT AUTO_INCREMENT PRIMARY KEY,order_id INT NOT NULL,product_id INT,product_name VARCHAR(160),qty INT NOT NULL,price DECIMAL(15,2) NOT NULL,FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE CASCADE);
CREATE TABLE product_change_requests(id INT AUTO_INCREMENT PRIMARY KEY,product_id INT NOT NULL,user_id INT NOT NULL,payload JSON NOT NULL,status ENUM('Pending','Approved','Rejected') DEFAULT 'Pending',reviewed_by INT NULL,reviewed_at DATETIME NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE audit_logs(id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,action VARCHAR(40),entity VARCHAR(80),entity_id INT,detail TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

-- password_hash values are generated using PHP password_hash().
INSERT INTO users(username,password_hash,name,role) VALUES
('adminweb','$2y$12$MaC.Ss.40fdEs2bntW70w.RRNyWGDk60E9UnExVVDCS31CC3WoBfa','Administrator','admin'),
('view','$2y$10$92IXUNpkj0rOQ5byMi.Ye4oKoEa3Ro9llC9W9kLr5tK6FJYQ6H6W','Operator View','view');
-- IMPORTANT: replace seed password hashes with hashes generated by password_hash() before production.

INSERT INTO shop_settings(shop_name,address,phone,email,maps_embed) VALUES
('Madang Store','Jl. Contoh No. 1, Jakarta','081234567890','info@example.com','https://www.google.com/maps/embed?pb=');

INSERT INTO products(name,stock,category,size,weight,height,price,code,image) VALUES
('Kaos Basic',25,'Fashion','L',250,3,85000,'BRG-001',''),
('Hoodie Minimal',12,'Fashion','XL',650,5,175000,'BRG-002',''),
('Totebag Canvas',30,'Aksesoris','All Size',300,4,65000,'BRG-003','');
INSERT INTO sliders(title,subtitle,image,sort_order) VALUES ('Belanja Mudah','Produk pilihan dengan harga terbaik','',1);