

CREATE TABLE IF NOT EXISTS customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(191) NOT NULL,
  company VARCHAR(191) NULL,
  wa VARCHAR(32) NOT NULL,
  email VARCHAR(191) NULL,
  address TEXT NULL,
  opt_in TINYINT(1) DEFAULT 0,
  opt_in_source VARCHAR(191) NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_tenant (tenant_id),
  KEY idx_tenant_wa (tenant_id, wa),
  KEY idx_tenant_name (tenant_id, name)
);


CREATE TABLE IF NOT EXISTS customer_types (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  type_code VARCHAR(32) NOT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_tenant_customer (tenant_id, customer_id),
  KEY idx_type (type_code)
);


CREATE TABLE IF NOT EXISTS customer_deposit_ledger (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  direction ENUM('in','out') NOT NULL,
  amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  ref_type VARCHAR(64) NULL,
  ref_id BIGINT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_tenant_customer (tenant_id, customer_id),
  KEY idx_ref (ref_type, ref_id)
);
