-- KoperasiMart POS additions (DB: inventory)
-- Jalankan di phpMyAdmin / mysql CLI pada database `inventory`.

CREATE TABLE IF NOT EXISTS nota_seq (
  prefix CHAR(5) PRIMARY KEY,
  last_seq INT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS jualkassa_hdr (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  client_txn_id CHAR(36) NOT NULL UNIQUE,
  outlet_id TINYINT NOT NULL,
  kassa TINYINT NULL,
  kasir VARCHAR(32) NULL,
  nota CHAR(10) NOT NULL UNIQUE,
  tgl DATE NOT NULL,
  jam TIME NOT NULL,
  total INT NOT NULL,
  discount INT NOT NULL DEFAULT 0,
  grand_total INT NOT NULL,
  dibayar INT NOT NULL DEFAULT 0,
  kembalian INT NOT NULL DEFAULT 0,
  viabayar VARCHAR(16) NOT NULL,
  pay_status ENUM('PAID','PENDING','FAILED') NOT NULL DEFAULT 'PAID',
  smartlink_trx_id VARCHAR(64) NULL,
  smartlink_channel VARCHAR(32) NULL,
  smartlink_payment_code VARCHAR(64) NULL,
  smartlink_expired_time VARCHAR(40) NULL,
  smartlink_qr_string MEDIUMTEXT NULL,
  notes VARCHAR(200) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_tgl_outlet (tgl, outlet_id),
  KEY idx_nota (nota),
  CONSTRAINT fk_hdr_outlet FOREIGN KEY (outlet_id) REFERENCES outlet(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS smartlink_callback_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  headers TEXT NULL,
  payload MEDIUMTEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Channel pembayaran Smartlink per outlet (opsional, auto dibuat oleh code)
CREATE TABLE IF NOT EXISTS payment_config (
  outlet_id TINYINT NOT NULL PRIMARY KEY,
  channels_csv VARCHAR(255) NOT NULL,
  default_channel VARCHAR(64) NOT NULL,
  updated_at DATETIME NULL,
  KEY idx_updated_at (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- OPTIONAL: pastikan outlet punya kolom NAMA (bukan name)
-- Bila kolom belum ada, jalankan:
-- ALTER TABLE outlet ADD COLUMN NAMA CHAR(30) NULL;
