-- BRAMBO platform — skema tahap 1 (MySQL 5.7+/8 atau MariaDB 10.3+)
-- Uang selalu BIGINT rupiah (tanpa desimal). Tanggal bisnis = DATE, waktu catat = DATETIME.
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS toko (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  kode  VARCHAR(10)  NOT NULL,
  nama  VARCHAR(80)  NOT NULL,
  aktif TINYINT(1)   NOT NULL DEFAULT 1,
  api_key_hash CHAR(64) NULL,           -- sha256 dari kunci sinkron Apps Script kasir
  UNIQUE KEY uq_toko_kode (kode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id              INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nik             VARCHAR(20)  NOT NULL,
  nama            VARCHAR(80)  NOT NULL,
  role            ENUM('owner','pic','kasir','gudang') NOT NULL,
  toko_id         INT UNSIGNED NULL,
  pin_hash        VARCHAR(255) NOT NULL,
  aktif           TINYINT(1)   NOT NULL DEFAULT 1,
  lihat_saldo     TINYINT(1)   NOT NULL DEFAULT 0,   -- hanya akun ber-izin yang boleh melihat saldo/mutasi brangkas
  gagal           INT UNSIGNED NOT NULL DEFAULT 0,
  terkunci_sampai DATETIME     NULL,
  login_terakhir  DATETIME     NULL,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_nik (nik),
  CONSTRAINT fk_users_toko FOREIGN KEY (toko_id) REFERENCES toko(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_log (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nik        VARCHAR(20)  NOT NULL,
  ip         VARCHAR(45)  NOT NULL,
  ok         TINYINT(1)   NOT NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_login_ip (ip, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS brangkas (
  id    INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nama  VARCHAR(60)  NOT NULL,
  saldo BIGINT       NOT NULL DEFAULT 0,   -- dijaga dalam transaksi yang sama dengan jurnal
  aktif TINYINT(1)   NOT NULL DEFAULT 1,
  UNIQUE KEY uq_brangkas_nama (nama)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS suppliers (
  id        INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nama      VARCHAR(120) NOT NULL,
  tab_lama  VARCHAR(120) NULL,          -- nama tab di spreadsheet Payment Suplier bsi (untuk impor)
  bank      VARCHAR(40)  NOT NULL DEFAULT '',
  rek_nama  VARCHAR(80)  NOT NULL DEFAULT '',
  rek_no    VARCHAR(40)  NOT NULL DEFAULT '',
  aktif     TINYINT(1)   NOT NULL DEFAULT 1,
  UNIQUE KEY uq_suppliers_nama (nama)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notas (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  supplier_id    INT UNSIGNED NOT NULL,
  tanggal_nota   DATE         NOT NULL,
  jumlah         BIGINT       NOT NULL,
  status         ENUM('belum','lunas') NOT NULL DEFAULT 'belum',
  metode         VARCHAR(30)  NULL,
  tanggal_bayar  DATE         NULL,
  catatan        VARCHAR(500) NOT NULL DEFAULT '',
  teks_tanggal   VARCHAR(255) NULL,     -- tanggal teks dari data lama (mis. "2, 4, 5 Agustus 2026")
  pembayaran_id  BIGINT UNSIGNED NULL,
  dibuat_oleh    INT UNSIGNED NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  void_at        DATETIME     NULL,
  void_oleh      INT UNSIGNED NULL,
  void_alasan    VARCHAR(255) NULL,
  KEY idx_notas_sup_status (supplier_id, status),
  KEY idx_notas_tanggal (tanggal_nota),
  KEY idx_notas_dup (supplier_id, tanggal_nota, jumlah),
  CONSTRAINT fk_notas_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT fk_notas_user FOREIGN KEY (dibuat_oleh) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pembayaran (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ref              VARCHAR(64)  NOT NULL,      -- kunci anti-dobel dari klien
  supplier_id      INT UNSIGNED NOT NULL,
  brangkas_id      INT UNSIGNED NOT NULL,
  jurnal_id        BIGINT UNSIGNED NULL,
  total            BIGINT       NOT NULL,      -- uang yang keluar dari brangkas
  potongan         BIGINT       NOT NULL DEFAULT 0,
  metode           VARCHAR(30)  NOT NULL,
  tanggal_bayar    DATE         NOT NULL,
  catatan_tambahan VARCHAR(120) NOT NULL DEFAULT '',
  dibuat_oleh      INT UNSIGNED NOT NULL,
  created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  dibatalkan       TINYINT(1)   NOT NULL DEFAULT 0,
  UNIQUE KEY uq_pembayaran_ref (ref),
  CONSTRAINT fk_pembayaran_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  CONSTRAINT fk_pembayaran_brangkas FOREIGN KEY (brangkas_id) REFERENCES brangkas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pembayaran_nota (
  pembayaran_id BIGINT UNSIGNED NOT NULL,
  nota_id       BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (pembayaran_id, nota_id),
  CONSTRAINT fk_pn_pembayaran FOREIGN KEY (pembayaran_id) REFERENCES pembayaran(id),
  CONSTRAINT fk_pn_nota FOREIGN KEY (nota_id) REFERENCES notas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS setoran (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  toko_id          INT UNSIGNED NOT NULL,
  tanggal          DATE         NOT NULL,        -- tanggal bisnis (shift 3 lewat tengah malam tetap ikut hari mulainya)
  shift_no         TINYINT UNSIGNED NOT NULL,
  nominal_sistem   BIGINT       NOT NULL,        -- angka dari kasir
  nominal_diterima BIGINT       NULL,            -- uang fisik yang dihitung PIC
  selisih          BIGINT       NULL,            -- diterima - sistem
  status           ENUM('menunggu','diterima','ditolak') NOT NULL DEFAULT 'menunggu',
  catatan_kasir    VARCHAR(255) NOT NULL DEFAULT '',
  catatan_pic      VARCHAR(255) NOT NULL DEFAULT '',
  dibuat_oleh      INT UNSIGNED NULL,
  kasir_nama       VARCHAR(80)  NOT NULL DEFAULT '',
  sumber           VARCHAR(10)  NOT NULL DEFAULT 'web',   -- web | gas
  created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  diproses_oleh    INT UNSIGNED NULL,
  diproses_at      DATETIME     NULL,
  jurnal_id        BIGINT UNSIGNED NULL,
  UNIQUE KEY uq_setoran_toko_shift (toko_id, tanggal, shift_no),
  KEY idx_setoran_status (status, tanggal),
  CONSTRAINT fk_setoran_toko FOREIGN KEY (toko_id) REFERENCES toko(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS jurnal (
  id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  kode           CHAR(36)     NOT NULL,
  brangkas_id    INT UNSIGNED NOT NULL,
  tanggal        DATE         NOT NULL,
  created_at     DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  jenis          ENUM('Masuk','Keluar') NOT NULL,
  nominal        BIGINT       NOT NULL,
  kategori       VARCHAR(40)  NOT NULL DEFAULT '',
  supplier_id    INT UNSIGNED NULL,
  keterangan     VARCHAR(500) NOT NULL DEFAULT '',
  pic_user_id    INT UNSIGNED NOT NULL,
  saldo_setelah  BIGINT       NOT NULL,
  dibatalkan     TINYINT(1)   NOT NULL DEFAULT 0,
  ref_koreksi_id BIGINT UNSIGNED NULL,
  transfer_pair  CHAR(36)     NULL,
  pembayaran_id  BIGINT UNSIGNED NULL,
  setoran_id     BIGINT UNSIGNED NULL,
  UNIQUE KEY uq_jurnal_kode (kode),
  KEY idx_jurnal_tgl (brangkas_id, tanggal),
  KEY idx_jurnal_pair (transfer_pair),
  CONSTRAINT fk_jurnal_brangkas FOREIGN KEY (brangkas_id) REFERENCES brangkas(id),
  CONSTRAINT fk_jurnal_pic FOREIGN KEY (pic_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opname (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  brangkas_id  INT UNSIGNED NOT NULL,
  saldo_sistem BIGINT       NOT NULL,
  saldo_fisik  BIGINT       NOT NULL,
  selisih      BIGINT       NOT NULL,
  keterangan   VARCHAR(255) NOT NULL DEFAULT '',
  pic_user_id  INT UNSIGNED NOT NULL,
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_opname_brangkas FOREIGN KEY (brangkas_id) REFERENCES brangkas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_log (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NULL,
  aksi       VARCHAR(60)  NOT NULL,
  entitas    VARCHAR(40)  NOT NULL,
  entitas_id VARCHAR(40)  NOT NULL DEFAULT '',
  detail     TEXT         NULL,
  ip         VARCHAR(45)  NOT NULL DEFAULT '',
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_ent (entitas, entitas_id),
  KEY idx_audit_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;