-- Kevin Cutting Akrilik - Baby Board Order
-- Jalankan di phpMyAdmin

CREATE TABLE IF NOT EXISTS settings (
  skey        VARCHAR(50)  NOT NULL,
  svalue      VARCHAR(255) DEFAULT NULL,
  updated_at  DATETIME     DEFAULT NULL,
  PRIMARY KEY (skey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO settings (skey, svalue, updated_at) VALUES
('harga_standar',   '165000', NOW()),
('harga_190k',      '190000', NOW()),
('harga_papan',     '130000', NOW()),
('harga_set_angka', '10000',  NOW()),
('harga_standing',  '10000',  NOW());

CREATE TABLE IF NOT EXISTS orders (
  id               INT          NOT NULL AUTO_INCREMENT,
  no_order         INT          NOT NULL,
  tgl_order        DATE         NOT NULL,
  nama_bayi        TEXT         NOT NULL,
  gender           ENUM('BOY','GIRL','-') NOT NULL DEFAULT 'BOY',
  nama_ortu        TEXT         DEFAULT NULL,
  tier             ENUM('STANDAR','190K','PAPAN') NOT NULL DEFAULT 'STANDAR',
  set_angka        TINYINT(1)   NOT NULL DEFAULT 0,
  standing         TINYINT(1)   NOT NULL DEFAULT 0,
  urgent           TINYINT(1)   NOT NULL DEFAULT 0,
  data_lahir       TINYINT(1)   NOT NULL DEFAULT 0,
  bb               VARCHAR(20)  DEFAULT NULL,
  pb               VARCHAR(20)  DEFAULT NULL,
  tgl_lahir        DATE         DEFAULT NULL,
  jam_lahir        VARCHAR(10)  DEFAULT NULL,
  desain_dasar     VARCHAR(150) DEFAULT NULL,
  ref_order        VARCHAR(50)  DEFAULT NULL,
  keterangan       TEXT         DEFAULT NULL,
  catatan_internal TEXT         DEFAULT NULL,
  harga_dasar      INT          NOT NULL DEFAULT 0,
  harga_angka      INT          NOT NULL DEFAULT 0,
  harga_standing   INT          NOT NULL DEFAULT 0,
  harga_lain       INT          NOT NULL DEFAULT 0,
  harga_lain_ket   VARCHAR(150) DEFAULT NULL,
  harga_total      INT          NOT NULL DEFAULT 0,
  status           ENUM('TO DESIGN','ACC DESIGN','IN PROGRESS','DONE','DIKIRIM') NOT NULL DEFAULT 'TO DESIGN',
  batch_id         INT          DEFAULT NULL,
  created_at       DATETIME     NOT NULL,
  created_by       VARCHAR(50)  DEFAULT NULL,
  updated_at       DATETIME     DEFAULT NULL,
  updated_by       VARCHAR(50)  DEFAULT NULL,
  deleted_at       DATETIME     DEFAULT NULL,
  deleted_by       VARCHAR(50)  DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_no_order (no_order),
  KEY idx_status (status),
  KEY idx_batch (batch_id),
  KEY idx_deleted (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_files (
  id          INT          NOT NULL AUTO_INCREMENT,
  order_id    INT          NOT NULL,
  file_name   VARCHAR(150) NOT NULL,
  orig_name   VARCHAR(255) DEFAULT NULL,
  mime        VARCHAR(80)  DEFAULT NULL,
  size        INT          NOT NULL DEFAULT 0,
  created_at  DATETIME     NOT NULL,
  created_by  VARCHAR(50)  DEFAULT NULL,
  deleted_at  DATETIME     DEFAULT NULL,
  deleted_by  VARCHAR(50)  DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id          INT          NOT NULL AUTO_INCREMENT,
  tabel       VARCHAR(50)  NOT NULL,
  record_id   INT          NOT NULL,
  aksi        VARCHAR(20)  NOT NULL,
  data_before LONGTEXT     DEFAULT NULL,
  data_after  LONGTEXT     DEFAULT NULL,
  user        VARCHAR(50)  DEFAULT NULL,
  created_at  DATETIME     NOT NULL,
  PRIMARY KEY (id),
  KEY idx_rec (tabel, record_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;