-- =========================================================
-- 019_pos_payments_and_settings.sql
-- Sprint 4 (POS Order Lanjutan)
-- - Payment log (partial/lunas)
-- - Overpayment -> customer deposit
-- - POS settings (rekening transfer + QRIS PNG)
-- =========================================================

START TRANSACTION;

-- ---------------------------------------------------------
-- 1) Orders: payment snapshot fields
-- (safe idempotent - tidak error kalau kolom sudah ada)
-- ---------------------------------------------------------

SET @db := DATABASE();

SET @c_paid := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='paid_amount'
);
SET @sql_paid := IF(@c_paid=0,
  'ALTER TABLE orders ADD COLUMN paid_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00',
  'SELECT 1'
);
PREPARE stmt FROM @sql_paid; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c_ps := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='payment_status'
);
SET @sql_ps := IF(@c_ps=0,
  "ALTER TABLE orders ADD COLUMN payment_status VARCHAR(20) NOT NULL DEFAULT ''",
  'SELECT 1'
);
PREPARE stmt FROM @sql_ps; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @c_dep := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='orders' AND COLUMN_NAME='deposit_added_amount'
);
SET @sql_dep := IF(@c_dep=0,
  'ALTER TABLE orders ADD COLUMN deposit_added_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00',
  'SELECT 1'
);
PREPARE stmt FROM @sql_dep; EXECUTE stmt; DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------
-- 2) Order Payments: payment log
-- ---------------------------------------------------------

CREATE TABLE IF NOT EXISTS order_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NOT NULL,

  method VARCHAR(30) NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  ref VARCHAR(120) NULL,
  note VARCHAR(255) NULL,

  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NULL,

  PRIMARY KEY (id),
  KEY idx_op_order (order_id),
  KEY idx_op_tenant_location (tenant_id, location_id),
  KEY idx_op_created_at (created_at),

  CONSTRAINT fk_op_order FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_op_user FOREIGN KEY (created_by) REFERENCES users(id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------
-- 3) POS Settings: rekening transfer + QRIS image
-- ---------------------------------------------------------

CREATE TABLE IF NOT EXISTS pos_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,

  transfer_bank_name VARCHAR(80) NULL,
  transfer_account_name VARCHAR(120) NULL,
  transfer_account_number VARCHAR(60) NULL,
  qris_image VARCHAR(255) NULL,

  created_at DATETIME NULL,
  updated_at DATETIME NULL,

  PRIMARY KEY (id),
  UNIQUE KEY uq_pos_settings_location (tenant_id, location_id),

  CONSTRAINT fk_pos_settings_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_pos_settings_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------
-- 4) Permissions (optional but recommended)
-- ---------------------------------------------------------

INSERT IGNORE INTO permissions (code, name, module, created_at) VALUES
('settings.pos.view','Lihat Pengaturan POS','SETTINGS',NOW()),
('settings.pos.edit','Edit Pengaturan POS','SETTINGS',NOW());


COMMIT;