CREATE DATABASE IF NOT EXISTS awinet
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE awinet;

CREATE TABLE IF NOT EXISTS service_regions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_service_regions_name (name)
) ENGINE=InnoDB;

INSERT INTO service_regions (name) VALUES
  ('Wilayah Barat'),
  ('Wilayah Tengah'),
  ('Wilayah Timur'),
  ('Wilayah Cisirih'),
  ('Wilayah Curugcina')
ON DUPLICATE KEY UPDATE name = VALUES(name);

CREATE TABLE IF NOT EXISTS packages (
  id VARCHAR(64) NOT NULL,
  name VARCHAR(120) NOT NULL,
  bandwidth VARCHAR(40) NOT NULL,
  speed_mbps SMALLINT UNSIGNED NULL,
  price BIGINT UNSIGNED NOT NULL,
  category VARCHAR(80) NOT NULL DEFAULT 'Reguler',
  badge VARCHAR(80) NULL,
  ideal_for VARCHAR(160) NULL,
  description TEXT NULL,
  features JSON NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_packages_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admin_accounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(80) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(20) NULL,
  role ENUM('centralAdmin', 'areaAdmin', 'technician') NOT NULL,
  region_id BIGINT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_admin_accounts_username (username),
  KEY idx_admin_accounts_region_role (region_id, role),
  CONSTRAINT fk_admin_accounts_region
    FOREIGN KEY (region_id) REFERENCES service_regions (id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS clients (
  id VARCHAR(32) NOT NULL,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  nik CHAR(16) NULL,
  email VARCHAR(120) NULL,
  address VARCHAR(500) NOT NULL,
  region_id BIGINT UNSIGNED NULL,
  requested_region_id BIGINT UNSIGNED NULL,
  registration_submitted_by VARCHAR(120) NULL,
  registration_submitted_at DATETIME NULL,
  package_id VARCHAR(64) NULL,
  package_name VARCHAR(120) NOT NULL,
  bandwidth VARCHAR(40) NOT NULL,
  price BIGINT UNSIGNED NOT NULL,
  billing_cycle TINYINT UNSIGNED NOT NULL DEFAULT 1,
  due_day TINYINT UNSIGNED NOT NULL DEFAULT 10,
  install_date DATE NULL,
  gps VARCHAR(160) NULL,
  landmark VARCHAR(255) NULL,
  notes VARCHAR(300) NULL,
  signature_data MEDIUMTEXT NULL,
  status VARCHAR(60) NOT NULL DEFAULT 'Menunggu Survey',
  registration_approval_status VARCHAR(40) NOT NULL DEFAULT 'Menunggu Persetujuan',
  password_hash VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_clients_phone_region (region_id, phone),
  KEY idx_clients_name (name),
  KEY idx_clients_region_status (region_id, status),
  KEY idx_clients_approval_status (registration_approval_status),
  CONSTRAINT fk_clients_region
    FOREIGN KEY (region_id) REFERENCES service_regions (id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_clients_requested_region
    FOREIGN KEY (requested_region_id) REFERENCES service_regions (id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_clients_package
    FOREIGN KEY (package_id) REFERENCES packages (id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS client_payments (
  id VARCHAR(100) NOT NULL,
  client_id VARCHAR(32) NOT NULL,
  month CHAR(7) NOT NULL,
  amount BIGINT UNSIGNED NOT NULL,
  method VARCHAR(60) NOT NULL,
  reference VARCHAR(255) NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'Menunggu Persetujuan Area & Pusat',
  area_approved_at DATETIME NULL,
  area_approved_by VARCHAR(120) NULL,
  central_approved_at DATETIME NULL,
  central_approved_by VARCHAR(120) NULL,
  rejection_note VARCHAR(500) NULL,
  submitted_at DATETIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_client_payments_client_month (client_id, month),
  KEY idx_client_payments_status (status),
  KEY idx_client_payments_submitted_at (submitted_at),
  CONSTRAINT fk_client_payments_client
    FOREIGN KEY (client_id) REFERENCES clients (id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS area_finance_records (
  id VARCHAR(64) NOT NULL,
  region_id BIGINT UNSIGNED NOT NULL,
  type ENUM('income', 'expense') NOT NULL,
  amount BIGINT UNSIGNED NOT NULL,
  category VARCHAR(120) NOT NULL,
  note VARCHAR(500) NULL,
  transaction_date DATE NOT NULL,
  vat_applicable TINYINT(1) NOT NULL DEFAULT 0,
  vat_amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_by VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_area_finance_region_date (region_id, transaction_date),
  CONSTRAINT fk_area_finance_region
    FOREIGN KEY (region_id) REFERENCES service_regions (id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS chat_conversations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  visitor_token CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  visitor_name VARCHAR(100) NOT NULL,
  status ENUM('open', 'closed') NOT NULL DEFAULT 'open',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_chat_conversations_visitor_token (visitor_token),
  KEY idx_chat_conversations_status_updated (status, updated_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS chat_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED NOT NULL,
  sender ENUM('visitor', 'admin') NOT NULL,
  sender_name VARCHAR(100) NOT NULL,
  message TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_chat_messages_conversation_created (conversation_id, created_at),
  CONSTRAINT fk_chat_messages_conversation
    FOREIGN KEY (conversation_id) REFERENCES chat_conversations (id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS chat_typing_status (
  conversation_id BIGINT UNSIGNED NOT NULL,
  admin_typing_until DATETIME NULL,
  PRIMARY KEY (conversation_id),
  CONSTRAINT fk_chat_typing_conversation
    FOREIGN KEY (conversation_id) REFERENCES chat_conversations (id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;