-- ====================================================================
-- 004_master_patch4_customers.sql — Sprint 3 Patch 4 (Pelanggan)
-- Master Data: Customers + Types + Deposit Ledger + WA normalization
-- ====================================================================


-- Customer Types
CREATE TABLE IF NOT EXISTS customer_types (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(64) NOT NULL,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customer_types_tenant_code (tenant_id, code),
  KEY idx_customer_types_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Customers
CREATE TABLE IF NOT EXISTS customers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  wa VARCHAR(32) NULL,
  email VARCHAR(190) NULL,
  note TEXT NULL,
  opt_in_wa TINYINT(1) NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_customers_tenant (tenant_id),
  KEY idx_customers_active (tenant_id, is_active),
  KEY idx_customers_wa (tenant_id, wa)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Customer Type Map (multi-type checklist)
CREATE TABLE IF NOT EXISTS customer_type_map (
  customer_id BIGINT UNSIGNED NOT NULL,
  type_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (customer_id, type_id),
  KEY idx_ctm_type (type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Deposit Ledger
CREATE TABLE IF NOT EXISTS customer_deposit_ledger (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  source VARCHAR(32) NOT NULL DEFAULT 'topup',
  ref VARCHAR(64) NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cdl_customer (tenant_id, customer_id),
  KEY idx_cdl_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ====================================================================
-- Permission catalog: master.customers.*
-- ====================================================================
INSERT IGNORE INTO permissions (code, module, action, name) VALUES
('master.customers.view','master.customers','view','Lihat Pelanggan'),
('master.customers.add','master.customers','add','Tambah Pelanggan'),
('master.customers.edit','master.customers','edit','Edit Pelanggan'),
('master.customers.print','master.customers','print','Print Data Pelanggan'),
('master.customers.export','master.customers','export','Export Data Pelanggan');

-- Auto grant to owner + admin
INSERT IGNORE INTO role_permissions(role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p
WHERE r.tenant_id = 1
  AND r.code IN ('owner','admin')
  AND p.code LIKE 'master.customers.%';

-- ====================================================================
-- Seed default customer types (tenant 1)
-- ====================================================================
INSERT IGNORE INTO customer_types (tenant_id, code, name, is_active) VALUES
(1,'retail','Retail',1),
(1,'agent','Agent',1),
(1,'corporate','Corporate',1),
(1,'vendor','Vendor',1),
(1,'supplier','Supplier',1);
