CREATE TABLE IF NOT EXISTS admin_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(120) NOT NULL,
  role VARCHAR(40) NOT NULL DEFAULT 'qc_user',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_variants (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(60) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  page_width_mm DECIMAL(8,2) NOT NULL,
  page_height_mm DECIMAL(8,2) NOT NULL,
  cover_width_mm DECIMAL(8,2) NOT NULL,
  cover_height_mm DECIMAL(8,2) NOT NULL,
  spine_width_mm DECIMAL(8,2) NOT NULL DEFAULT 6.00,
  min_short_side_px INT UNSIGNED NOT NULL DEFAULT 2500,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS projects (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_code VARCHAR(80) NOT NULL UNIQUE,
  customer_name VARCHAR(120) NOT NULL,
  whatsapp VARCHAR(30) NULL,
  package_count INT UNSIGNED NOT NULL DEFAULT 20,
  variant_id INT UNSIGNED NOT NULL,
  theme_code VARCHAR(50) NOT NULL DEFAULT 'classic',
  cover_front_title VARCHAR(180) NULL,
  cover_back_title VARCHAR(180) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'awaiting_upload',
  pdf_status VARCHAR(40) NOT NULL DEFAULT 'idle',
  uploaded_count INT UNSIGNED NOT NULL DEFAULT 0,
  required_count INT UNSIGNED NOT NULL DEFAULT 20,
  layout_json LONGTEXT NULL,
  qc_notes TEXT NULL,
  pdf_content_path VARCHAR(255) NULL,
  pdf_cover_path VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_status (status),
  CONSTRAINT fk_projects_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS project_photos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id INT UNSIGNED NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  width_px INT UNSIGNED NOT NULL,
  height_px INT UNSIGNED NOT NULL,
  orientation VARCHAR(20) NOT NULL,
  sort_no INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_project_sort (project_id, sort_no),
  CONSTRAINT fk_photos_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pdf_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  project_id INT UNSIGNED NOT NULL,
  job_kind VARCHAR(20) NOT NULL DEFAULT 'both',
  status VARCHAR(20) NOT NULL DEFAULT 'queued',
  output_content_path VARCHAR(255) NULL,
  output_cover_path VARCHAR(255) NULL,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_job_status (status),
  CONSTRAINT fk_jobs_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
