-- Lumero Order v1.1
-- MySQL 8.0+ / MariaDB 10.5+ (InnoDB, utf8mb4)
CREATE DATABASE IF NOT EXISTS lumero_order
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE lumero_order;

-- Import this schema into a NEW, empty database.

CREATE TABLE admins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  whatsapp VARCHAR(30) NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_admin_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE roles (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255) NULL
) ENGINE=InnoDB;

CREATE TABLE admin_roles (
  admin_id BIGINT UNSIGNED NOT NULL,
  role_id SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (admin_id, role_id),
  CONSTRAINT fk_admin_roles_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
  CONSTRAINT fk_admin_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE api_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  device_name VARCHAR(120) NULL,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tokens_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
  INDEX idx_tokens_admin (admin_id),
  INDEX idx_tokens_expiry (expires_at, revoked_at)
) ENGINE=InnoDB;

CREATE TABLE exhibitions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(180) NOT NULL,
  venue VARCHAR(180) NULL,
  city VARCHAR(120) NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status ENUM('planned','active','completed','cancelled') NOT NULL DEFAULT 'planned',
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_exhibitions_admin FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_exhibitions_dates (start_date, end_date),
  INDEX idx_exhibitions_status (status)
) ENGINE=InnoDB;

CREATE TABLE customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  whatsapp VARCHAR(30) NOT NULL,
  email VARCHAR(190) NULL,
  shipping_address TEXT NULL,
  city VARCHAR(120) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_customers_name (name),
  INDEX idx_customers_whatsapp (whatsapp)
) ENGINE=InnoDB;

CREATE TABLE products (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(80) NULL UNIQUE,
  name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_products_name (name)
) ENGINE=InnoDB;

CREATE TABLE product_variants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id BIGINT UNSIGNED NOT NULL,
  variant_name VARCHAR(100) NOT NULL,
  size_label VARCHAR(30) NULL,
  color VARCHAR(80) NULL,
  sku VARCHAR(80) NULL UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_variants_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
  INDEX idx_variants_product (product_id),
  INDEX idx_variants_size (size_label)
) ENGINE=InnoDB;

CREATE TABLE sales_orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_number VARCHAR(40) NOT NULL UNIQUE,
  exhibition_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  order_date DATETIME NOT NULL,
  fulfillment_type ENUM('take_at_exhibition','delivery_later') NOT NULL,
  estimated_delivery_date DATE NULL,
  factory_order_date DATE NULL,
  delivery_status ENUM('not_required','waiting','packing','shipped','delivered','delivery_issue') NOT NULL DEFAULT 'waiting',
  delivery_address TEXT NULL,
  courier_name VARCHAR(100) NULL,
  tracking_number VARCHAR(120) NULL,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_exhibition FOREIGN KEY (exhibition_id) REFERENCES exhibitions(id) ON DELETE RESTRICT,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_orders_created_admin FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL,
  CONSTRAINT fk_orders_updated_admin FOREIGN KEY (updated_by) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_orders_exhibition_date (exhibition_id, order_date),
  INDEX idx_orders_customer (customer_id),
  INDEX idx_orders_delivery (delivery_status),
  INDEX idx_orders_estimated_delivery (estimated_delivery_date)
) ENGINE=InnoDB;

CREATE TABLE sales_order_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  variant_id BIGINT UNSIGNED NULL,
  quantity SMALLINT UNSIGNED NOT NULL,
  customization_note TEXT NULL,
  production_status ENUM('new','verified','queued','in_progress','qc','ready_to_ship','completed','on_hold','rework') NOT NULL DEFAULT 'new',
  item_notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES sales_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
  CONSTRAINT fk_items_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL,
  CONSTRAINT chk_item_quantity CHECK (quantity > 0),
  INDEX idx_items_order (order_id),
  INDEX idx_items_product (product_id),
  INDEX idx_items_production_status (production_status)
) ENGINE=InnoDB;

CREATE TABLE production_steps (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  sequence_no SMALLINT UNSIGNED NOT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_production_sequence (sequence_no)
) ENGINE=InnoDB;

CREATE TABLE production_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_id BIGINT UNSIGNED NOT NULL UNIQUE,
  job_number VARCHAR(50) NOT NULL UNIQUE,
  status ENUM('pending','queued','in_progress','qc','ready','completed','on_hold','cancelled') NOT NULL DEFAULT 'pending',
  priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  planned_start_date DATE NULL,
  target_finish_date DATE NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_jobs_item FOREIGN KEY (item_id) REFERENCES sales_order_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_jobs_admin FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_jobs_status_target (status, target_finish_date)
) ENGINE=InnoDB;

CREATE TABLE production_job_steps (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  production_job_id BIGINT UNSIGNED NOT NULL,
  production_step_id SMALLINT UNSIGNED NOT NULL,
  status ENUM('pending','in_progress','passed','failed','skipped') NOT NULL DEFAULT 'pending',
  assigned_admin_id BIGINT UNSIGNED NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  note TEXT NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_job_steps_job FOREIGN KEY (production_job_id) REFERENCES production_jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_job_steps_step FOREIGN KEY (production_step_id) REFERENCES production_steps(id) ON DELETE RESTRICT,
  CONSTRAINT fk_job_steps_assigned FOREIGN KEY (assigned_admin_id) REFERENCES admins(id) ON DELETE SET NULL,
  CONSTRAINT fk_job_steps_updated FOREIGN KEY (updated_by) REFERENCES admins(id) ON DELETE SET NULL,
  UNIQUE KEY uq_job_step (production_job_id, production_step_id),
  INDEX idx_job_steps_status (status),
  INDEX idx_job_steps_assigned (assigned_admin_id)
) ENGINE=InnoDB;

CREATE TABLE production_activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  production_job_id BIGINT UNSIGNED NOT NULL,
  job_step_id BIGINT UNSIGNED NULL,
  admin_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  old_status VARCHAR(40) NULL,
  new_status VARCHAR(40) NULL,
  note TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_prod_logs_job FOREIGN KEY (production_job_id) REFERENCES production_jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_prod_logs_job_step FOREIGN KEY (job_step_id) REFERENCES production_job_steps(id) ON DELETE SET NULL,
  CONSTRAINT fk_prod_logs_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_prod_logs_job_date (production_job_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE order_activity_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  admin_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  description TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_order_logs_order FOREIGN KEY (order_id) REFERENCES sales_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_order_logs_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_order_logs_order_date (order_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE deliveries (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  status ENUM('waiting','packing','shipped','delivered','delivery_issue') NOT NULL DEFAULT 'waiting',
  courier_name VARCHAR(100) NULL,
  tracking_number VARCHAR(120) NULL,
  shipped_at DATETIME NULL,
  delivered_at DATETIME NULL,
  recipient_name VARCHAR(150) NULL,
  delivery_note TEXT NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_deliveries_order FOREIGN KEY (order_id) REFERENCES sales_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_deliveries_admin FOREIGN KEY (updated_by) REFERENCES admins(id) ON DELETE SET NULL,
  INDEX idx_deliveries_status (status),
  INDEX idx_deliveries_tracking (tracking_number)
) ENGINE=InnoDB;

INSERT INTO roles (name, description) VALUES
('superadmin','Akses penuh seluruh sistem'),
('admin','Kelola pameran, customer, produk dan order'),
('production','Kelola antrean dan tahapan produksi'),
('qc','Pemeriksaan kualitas produk'),
('delivery','Kelola packing, resi dan pengiriman'),
('viewer','Hanya melihat data dan laporan')
ON DUPLICATE KEY UPDATE description = VALUES(description);

INSERT INTO production_steps (code, name, sequence_no, is_required) VALUES
('verification','Verifikasi order',1,1),
('cutting','Cutting / pemotongan',2,1),
('stitching','Stitching / penjahitan',3,1),
('customization','Customization',4,0),
('finishing','Finishing',5,1),
('qc','Quality Control',6,1),
('packing','Packing',7,1);

-- Create first administrator using the PHP CLI command:
-- php bin/create-admin.php