-- =========================================================
-- 046_customer_member_classes.sql
-- Tahap 1 Class Member Retail
-- =========================================================

START TRANSACTION;

CREATE TABLE IF NOT EXISTS member_classes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  code VARCHAR(60) NOT NULL,
  description TEXT NULL,
  rank_priority INT NOT NULL DEFAULT 0,
  badge_color VARCHAR(30) NOT NULL DEFAULT '#6b7280',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_auto_assign TINYINT(1) NOT NULL DEFAULT 1,
  allow_manual_override TINYINT(1) NOT NULL DEFAULT 1,
  allow_manual_lock TINYINT(1) NOT NULL DEFAULT 1,
  min_order_count INT NOT NULL DEFAULT 0,
  min_total_spending DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  evaluation_period_days INT NOT NULL DEFAULT 365,
  max_days_since_last_order INT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_member_classes_scope_code (tenant_id, location_id, code),
  KEY idx_member_classes_scope_active (tenant_id, location_id, is_active, rank_priority),
  CONSTRAINT fk_member_classes_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_member_classes_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS customer_member_class_assignments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  member_class_id BIGINT UNSIGNED NULL,
  source VARCHAR(30) NOT NULL DEFAULT 'auto',
  assigned_at DATETIME NULL,
  notes TEXT NULL,
  is_locked TINYINT(1) NOT NULL DEFAULT 0,
  last_evaluated_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customer_member_assignment_scope_customer (tenant_id, location_id, customer_id),
  KEY idx_customer_member_assignment_scope_class (tenant_id, location_id, member_class_id),
  CONSTRAINT fk_cmca_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cmca_member_class FOREIGN KEY (member_class_id) REFERENCES member_classes(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_cmca_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cmca_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS customer_member_class_evaluations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  previous_member_class_id BIGINT UNSIGNED NULL,
  evaluated_member_class_id BIGINT UNSIGNED NULL,
  applied_member_class_id BIGINT UNSIGNED NULL,
  action_type VARCHAR(40) NOT NULL DEFAULT 'preview',
  evaluation_source VARCHAR(30) NOT NULL DEFAULT 'auto',
  is_preview TINYINT(1) NOT NULL DEFAULT 1,
  is_applied TINYINT(1) NOT NULL DEFAULT 0,
  is_locked_skip TINYINT(1) NOT NULL DEFAULT 0,
  reason_summary TEXT NULL,
  metrics_json LONGTEXT NULL,
  evaluated_at DATETIME NULL,
  created_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_cmce_scope_customer (tenant_id, location_id, customer_id, evaluated_at),
  CONSTRAINT fk_cmce_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cmce_prev_class FOREIGN KEY (previous_member_class_id) REFERENCES member_classes(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_cmce_eval_class FOREIGN KEY (evaluated_member_class_id) REFERENCES member_classes(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_cmce_applied_class FOREIGN KEY (applied_member_class_id) REFERENCES member_classes(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_cmce_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_cmce_location FOREIGN KEY (location_id) REFERENCES locations(id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, name, module, created_at) VALUES
('member_classes.view', 'Lihat Master Class Member', 'customers', NOW()),
('member_classes.manage', 'Kelola Master Class Member', 'customers', NOW()),
('customers.member_class.manage', 'Kelola Assignment Class Member', 'customers', NOW());

COMMIT;