SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+08:00";
CREATE TABLE IF NOT EXISTS businesses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  address TEXT NULL,
  phone VARCHAR(50) NULL,
  logo_url TEXT NULL,
  receipt_footer TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  business_id BIGINT UNSIGNED NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('super_admin','admin','manager','cashier','washer') NOT NULL DEFAULT 'cashier',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_users_business (business_id),
  CONSTRAINT fk_users_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS vehicle_types (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  business_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_vehicle_business (business_id),
  CONSTRAINT fk_vehicle_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS wash_orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  business_id BIGINT UNSIGNED NOT NULL,
  queue_no INT NOT NULL,
  plate_no VARCHAR(30) NOT NULL,
  customer_name VARCHAR(120) NULL,
  customer_phone VARCHAR(50) NULL,
  vehicle_type_id BIGINT UNSIGNED NOT NULL,
  handler_name VARCHAR(120) NOT NULL,
  entry_time DATETIME NOT NULL,
  finish_time DATETIME NULL,
  status ENUM('waiting','washing','done','cancelled') NOT NULL DEFAULT 'waiting',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  payment_method ENUM('cash','qris','transfer') NULL,
  payment_status ENUM('unpaid','paid','void') NOT NULL DEFAULT 'unpaid',
  paid_at DATETIME NULL,
  cashier_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_orders_business_date (business_id, entry_time),
  INDEX idx_orders_plate (plate_no),
  INDEX idx_orders_status (status,payment_status),
  CONSTRAINT fk_orders_business FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE,
  CONSTRAINT fk_orders_vehicle FOREIGN KEY (vehicle_type_id) REFERENCES vehicle_types(id),
  CONSTRAINT fk_orders_cashier FOREIGN KEY (cashier_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_orders_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  business_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  description TEXT NULL,
  ip_address VARCHAR(60) NULL,
  created_at DATETIME NULL,
  INDEX idx_audit_business (business_id),
  INDEX idx_audit_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO businesses (id,name,address,phone,receipt_footer,is_active,created_at,updated_at) VALUES
(1,'Demo Cuci Motor','Jl. Contoh No. 1, Denpasar','08123456789','Terima kasih. Semoga kendaraan Anda bersih dan nyaman digunakan.',1,NOW(),NOW());
INSERT INTO users (business_id,name,email,password,role,is_active,created_at,updated_at) VALUES
(NULL,'Super Admin','admin@cumot.local','$2y$12$ZB2Zx2C.8i3l8dut7sCyruPYpzkjJN2OcSlznVYfz5jCtqzPwPRXy','super_admin',1,NOW(),NOW()),
(1,'Admin Demo','demo@cumot.local','$2y$12$ZB2Zx2C.8i3l8dut7sCyruPYpzkjJN2OcSlznVYfz5jCtqzPwPRXy','admin',1,NOW(),NOW());
INSERT INTO vehicle_types (business_id,name,price,description,is_active,created_at,updated_at) VALUES
(1,'Motor Matic',15000,'Cuci motor standar',1,NOW(),NOW()),
(1,'Motor Bebek',15000,'Cuci motor standar',1,NOW(),NOW()),
(1,'Motor Sport / Besar',20000,'Cuci motor ukuran besar',1,NOW(),NOW()),
(1,'Mobil Kecil',35000,'Opsional jika usaha menerima mobil',1,NOW(),NOW());
COMMIT;