-- migrations/005_customers.sql

-- ==============================
-- SPRINT 3 — CUSTOMER (from zero)
-- ==============================

CREATE TABLE IF NOT EXISTS customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,

  -- Public identity
  id_number VARCHAR(60) NOT NULL,
  public_token VARCHAR(80) NOT NULL,

  -- Main data
  name VARCHAR(160) NOT NULL,
  company VARCHAR(160) NULL,
  wa VARCHAR(40) NOT NULL,
  email VARCHAR(160) NULL,
  address TEXT NULL,
  customer_types JSON NULL, -- multi: retail/agen/vendor/supplier
  opt_in TINYINT NOT NULL DEFAULT 0,
  is_active TINYINT NOT NULL DEFAULT 1,

  -- Membership (cached)
  member_class VARCHAR(20) NOT NULL DEFAULT 'reguler',
  member_score DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  member_period_months INT NOT NULL DEFAULT 12,
  member_updated_at DATETIME NULL,

  created_at DATETIME NULL,
  updated_at DATETIME NULL,

  UNIQUE KEY uq_customers_tenant_idnum (tenant_id, id_number),
  UNIQUE KEY uq_customers_token (public_token),
  INDEX idx_customers_tenant (tenant_id),
  INDEX idx_customers_wa (wa),
  CONSTRAINT fk_customers_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;


-- Membership rules per tenant (configurable, versioned)
CREATE TABLE IF NOT EXISTS customer_membership_rules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  effective_from DATE NOT NULL,
  period_months INT NOT NULL DEFAULT 12,
  gold_min DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  premium_min DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  platinum_min DECIMAL(18,2) NOT NULL DEFAULT 0.00,
  is_active TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_cmr_tenant_effective (tenant_id, effective_from),
  CONSTRAINT fk_cmr_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;


-- Deposit ledger (Retail)
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,
  note VARCHAR(255) NULL,
  ref_type VARCHAR(60) NULL,
  ref_id BIGINT NULL,
  created_at DATETIME NULL,
  INDEX idx_cdl_tenant_customer (tenant_id, customer_id),
  CONSTRAINT fk_cdl_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cdl_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;