-- Skema PostgreSQL: website jasa Fotografi, Desain Minimalis, dan Photobook
-- (Untuk MySQL: ganti BIGSERIAL -> BIGINT AUTO_INCREMENT, TIMESTAMPTZ -> DATETIME,
--  ENUM dibuat inline, dan JSONB -> JSON.)

CREATE TYPE user_role      AS ENUM ('admin', 'client');
CREATE TYPE category_type  AS ENUM ('photography', 'design', 'branding', 'photobook');
CREATE TYPE order_kind     AS ENUM ('photobook_order', 'photography_inquiry', 'design_inquiry');
CREATE TYPE order_status   AS ENUM ('new', 'quoted', 'confirmed', 'in_production', 'shipped', 'completed', 'cancelled');

CREATE TABLE users (
  id            BIGSERIAL PRIMARY KEY,
  name          VARCHAR(120)  NOT NULL,
  email         VARCHAR(190)  NOT NULL UNIQUE,
  password_hash VARCHAR(255),                      -- NULL untuk klien tamu
  role          user_role     NOT NULL DEFAULT 'client',
  phone         VARCHAR(30),
  created_at    TIMESTAMPTZ   NOT NULL DEFAULT now()
);

CREATE TABLE categories (
  id         BIGSERIAL PRIMARY KEY,
  name       VARCHAR(80)   NOT NULL,
  slug       VARCHAR(100)  NOT NULL UNIQUE,
  type       category_type NOT NULL,
  sort_order INT           NOT NULL DEFAULT 0
);

CREATE TABLE projects (
  id           BIGSERIAL PRIMARY KEY,
  category_id  BIGINT       NOT NULL REFERENCES categories(id),
  client_id    BIGINT       REFERENCES users(id) ON DELETE SET NULL,
  client_name  VARCHAR(150),                       -- untuk klien non-akun
  title        VARCHAR(180) NOT NULL,
  slug         VARCHAR(200) NOT NULL UNIQUE,
  description  TEXT,
  project_date DATE,
  is_featured  BOOLEAN      NOT NULL DEFAULT FALSE, -- tampil di hero Home
  is_published BOOLEAN      NOT NULL DEFAULT TRUE,
  created_at   TIMESTAMPTZ  NOT NULL DEFAULT now()
);
CREATE INDEX idx_projects_category ON projects(category_id, is_published, project_date DESC);
CREATE INDEX idx_projects_featured ON projects(is_featured) WHERE is_featured;

CREATE TABLE project_images (
  id         BIGSERIAL PRIMARY KEY,
  project_id BIGINT       NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  url        VARCHAR(500) NOT NULL,
  alt_text   VARCHAR(255),
  width      INT,                                   -- membantu layout masonry tanpa layout shift
  height     INT,
  sort_order INT          NOT NULL DEFAULT 0,
  is_cover   BOOLEAN      NOT NULL DEFAULT FALSE
);
CREATE INDEX idx_project_images_project ON project_images(project_id, sort_order);

CREATE TABLE photobooks (
  id            BIGSERIAL PRIMARY KEY,
  category_id   BIGINT        REFERENCES categories(id),
  title         VARCHAR(180)  NOT NULL,
  slug          VARCHAR(200)  NOT NULL UNIQUE,
  description   TEXT,
  size_label    VARCHAR(40)   NOT NULL,             -- mis. '21 x 21 cm'
  paper_type    VARCHAR(80)   NOT NULL,             -- mis. 'Matte art paper 170 gsm'
  binding_type  VARCHAR(80)   NOT NULL,             -- mis. 'Hardcover, jahit benang'
  page_count    INT           NOT NULL,
  price         NUMERIC(10,2) NOT NULL,
  currency      CHAR(3)       NOT NULL DEFAULT 'MYR',
  cover_url     VARCHAR(500)  NOT NULL,
  is_available  BOOLEAN       NOT NULL DEFAULT TRUE,
  created_at    TIMESTAMPTZ   NOT NULL DEFAULT now()
);

-- Halaman untuk fitur page-flip preview
CREATE TABLE photobook_pages (
  id           BIGSERIAL PRIMARY KEY,
  photobook_id BIGINT       NOT NULL REFERENCES photobooks(id) ON DELETE CASCADE,
  page_number  INT          NOT NULL,
  image_url    VARCHAR(500) NOT NULL,
  UNIQUE (photobook_id, page_number)
);

CREATE TABLE orders (
  id            BIGSERIAL PRIMARY KEY,
  kind          order_kind    NOT NULL,
  status        order_status  NOT NULL DEFAULT 'new',
  user_id       BIGINT        REFERENCES users(id) ON DELETE SET NULL,
  contact_name  VARCHAR(120)  NOT NULL,
  contact_email VARCHAR(190)  NOT NULL,
  contact_phone VARCHAR(30),
  photobook_id  BIGINT        REFERENCES photobooks(id),   -- terisi untuk photobook_order
  quantity      INT           NOT NULL DEFAULT 1 CHECK (quantity > 0),
  custom_spec   JSONB,                                    -- ukuran/kertas/jilid custom
  event_date    DATE,                                     -- untuk jasa foto
  message       TEXT,
  quoted_price  NUMERIC(10,2),
  created_at    TIMESTAMPTZ   NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ   NOT NULL DEFAULT now(),
  CHECK (kind <> 'photobook_order' OR photobook_id IS NOT NULL OR custom_spec IS NOT NULL)
);
CREATE INDEX idx_orders_status ON orders(status, created_at DESC);
CREATE INDEX idx_orders_email  ON orders(contact_email);

-- Data awal kategori
INSERT INTO categories (name, slug, type, sort_order) VALUES
  ('Photography',       'photography', 'photography', 1),
  ('Minimalist Design', 'design',      'design',      2),
  ('Branding',          'branding',    'branding',    3),
  ('Photobook',         'photobook',   'photobook',   4);