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

USE api_wa;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  full_name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL,
  password_hash VARCHAR(255) NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'customer',
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  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_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contacts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  full_name VARCHAR(150) NOT NULL,
  phone_number VARCHAR(32) NOT NULL,
  email VARCHAR(150) NULL,
  company_name VARCHAR(150) NULL,
  notes TEXT NULL,
  variables_json LONGTEXT NULL,
  source ENUM('manual', 'import') NOT NULL DEFAULT 'manual',
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  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_contacts_user_phone_number (user_id, phone_number),
  KEY idx_contacts_user_id (user_id),
  KEY idx_contacts_status (status),
  CONSTRAINT fk_contacts_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  group_name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  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_contact_groups_user_group_name (user_id, group_name),
  KEY idx_contact_groups_user_id (user_id),
  KEY idx_contact_groups_status (status),
  CONSTRAINT fk_contact_groups_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_group_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  contact_group_id BIGINT UNSIGNED NOT NULL,
  contact_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_contact_group_members_group_contact (contact_group_id, contact_id),
  KEY idx_contact_group_members_contact_id (contact_id),
  CONSTRAINT fk_contact_group_members_group
    FOREIGN KEY (contact_group_id) REFERENCES contact_groups (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_contact_group_members_contact
    FOREIGN KEY (contact_id) REFERENCES contacts (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS message_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  template_name VARCHAR(150) NOT NULL,
  content_type ENUM('message', 'button', 'poll', 'submission') NOT NULL DEFAULT 'message',
  body_text TEXT NOT NULL,
  structured_payload_json LONGTEXT NULL,
  notes TEXT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  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_message_templates_user_name (user_id, template_name),
  KEY idx_message_templates_user_id (user_id),
  KEY idx_message_templates_status_type (status, content_type),
  CONSTRAINT fk_message_templates_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS whatsapp_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  group_jid VARCHAR(191) NOT NULL,
  group_name VARCHAR(191) NOT NULL,
  owner_jid VARCHAR(191) NULL,
  participant_count INT UNSIGNED NOT NULL DEFAULT 0,
  source_channel ENUM('qr_unofficial', 'cloud_api') NOT NULL DEFAULT 'qr_unofficial',
  metadata_json LONGTEXT NULL,
  last_synced_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  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_whatsapp_groups_device_jid (device_id, group_jid),
  KEY idx_whatsapp_groups_user_id (user_id),
  KEY idx_whatsapp_groups_device_id (device_id),
  KEY idx_whatsapp_groups_group_name (group_name),
  CONSTRAINT fk_whatsapp_groups_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_whatsapp_groups_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS devices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  device_name VARCHAR(150) NOT NULL,
  channel_type ENUM('cloud_api', 'qr_unofficial') NOT NULL DEFAULT 'cloud_api',
  display_phone_number VARCHAR(32) NULL,
  phone_number_id VARCHAR(64) NULL,
  business_account_id VARCHAR(64) NULL,
  wa_jid VARCHAR(128) NULL,
  connection_status ENUM('draft', 'configured', 'connecting', 'qr_ready', 'connected', 'disconnected', 'error') NOT NULL DEFAULT 'draft',
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  last_connected_at DATETIME NULL,
  last_seen_at DATETIME NULL,
  last_error TEXT NULL,
  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_devices_phone_number_id (phone_number_id),
  UNIQUE KEY uq_devices_wa_jid (wa_jid),
  KEY idx_devices_user_id (user_id),
  KEY idx_devices_channel_type (channel_type),
  CONSTRAINT fk_devices_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_credentials (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id BIGINT UNSIGNED NOT NULL,
  access_token LONGTEXT NULL,
  verify_token VARCHAR(150) NULL,
  session_key VARCHAR(120) NULL,
  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_device_credentials_device_id (device_id),
  UNIQUE KEY uq_device_credentials_verify_token (verify_token),
  UNIQUE KEY uq_device_credentials_session_key (session_key),
  CONSTRAINT fk_device_credentials_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id BIGINT UNSIGNED NOT NULL,
  session_engine VARCHAR(50) NOT NULL DEFAULT 'cloud_api',
  session_status VARCHAR(50) NOT NULL DEFAULT 'idle',
  qr_code_data LONGTEXT NULL,
  qr_updated_at DATETIME NULL,
  session_path VARCHAR(255) NULL,
  last_boot_at DATETIME NULL,
  last_logout_at DATETIME NULL,
  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_device_sessions_device_id (device_id),
  CONSTRAINT fk_device_sessions_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_webhooks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id BIGINT UNSIGNED NOT NULL,
  webhook_path VARCHAR(150) NOT NULL DEFAULT '/webhook',
  connect_webhook_url VARCHAR(255) NULL,
  message_status_webhook_url VARCHAR(255) NULL,
  flow_webhook_url VARCHAR(255) NULL,
  webhook_secret VARCHAR(150) NULL,
  ip_whitelist TEXT NULL,
  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_device_webhooks_device_id (device_id),
  CONSTRAINT fk_device_webhooks_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id BIGINT UNSIGNED NOT NULL,
  auto_reply_enabled TINYINT(1) NOT NULL DEFAULT 1,
  auto_reply_template VARCHAR(255) NOT NULL DEFAULT 'Halo, pesan Anda diterima: "{message}"',
  chatbot_enabled TINYINT(1) NOT NULL DEFAULT 1,
  handle_personal_messages TINYINT(1) NOT NULL DEFAULT 1,
  handle_group_messages TINYINT(1) NOT NULL DEFAULT 0,
  inbox_enabled TINYINT(1) NOT NULL DEFAULT 1,
  response_source ENUM('autoreply', 'spreadsheet', 'flow', 'ai') NOT NULL DEFAULT 'autoreply',
  quick_reply_enabled TINYINT(1) NOT NULL DEFAULT 0,
  forward_attachment_enabled TINYINT(1) NOT NULL DEFAULT 0,
  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_device_settings_device_id (device_id),
  CONSTRAINT fk_device_settings_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS integration_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  integration_secret VARCHAR(180) NULL,
  api_key VARCHAR(180) NULL,
  api_base_url VARCHAR(255) NULL,
  ai_provider ENUM('none', 'openai', 'custom') NOT NULL DEFAULT 'none',
  ai_endpoint_url VARCHAR(255) NULL,
  ai_api_key VARCHAR(180) NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  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_integration_settings_user_id (user_id),
  KEY idx_integration_settings_status (status),
  CONSTRAINT fk_integration_settings_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS webhook_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(50) NOT NULL,
  payload LONGTEXT NOT NULL,
  processed TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_webhook_events_device_id (device_id),
  CONSTRAINT fk_webhook_events_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS webhook_deliveries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  event_type ENUM('connect_event', 'message_status', 'flow_event') NOT NULL DEFAULT 'message_status',
  source_record_type VARCHAR(50) NULL,
  source_record_id BIGINT UNSIGNED NULL,
  target_url VARCHAR(255) NOT NULL,
  http_method VARCHAR(10) NOT NULL DEFAULT 'POST',
  request_payload LONGTEXT NOT NULL,
  response_status_code SMALLINT UNSIGNED NULL,
  response_body LONGTEXT NULL,
  delivery_status ENUM('queued', 'sent', 'failed') NOT NULL DEFAULT 'queued',
  error_details LONGTEXT NULL,
  attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_attempt_at DATETIME NULL,
  delivered_at DATETIME NULL,
  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_webhook_deliveries_device_id (device_id),
  KEY idx_webhook_deliveries_user_id (user_id),
  KEY idx_webhook_deliveries_event_status (event_type, delivery_status),
  KEY idx_webhook_deliveries_created_at (created_at),
  CONSTRAINT fk_webhook_deliveries_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_webhook_deliveries_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inbound_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id BIGINT UNSIGNED NULL,
  webhook_event_id BIGINT UNSIGNED NULL,
  wa_message_id VARCHAR(128) NOT NULL,
  sender_wa_id VARCHAR(64) NOT NULL,
  sender_name VARCHAR(150) NULL,
  message_type VARCHAR(50) NOT NULL,
  message_body TEXT NULL,
  raw_payload LONGTEXT NOT NULL,
  received_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_inbound_messages_wa_message_id (wa_message_id),
  KEY idx_inbound_messages_device_id (device_id),
  KEY idx_inbound_messages_webhook_event_id (webhook_event_id),
  CONSTRAINT fk_inbound_messages_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_inbound_messages_webhook_event
    FOREIGN KEY (webhook_event_id) REFERENCES webhook_events (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS outbound_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id BIGINT UNSIGNED NULL,
  inbound_message_id BIGINT UNSIGNED NULL,
  template_id BIGINT UNSIGNED NULL,
  target_type ENUM('manual_number', 'contact', 'group', 'wa_group', 'import', 'spreadsheet') NOT NULL DEFAULT 'manual_number',
  target_label VARCHAR(180) NULL,
  recipient_wa_id VARCHAR(64) NOT NULL,
  context_message_id VARCHAR(128) NULL,
  message_type VARCHAR(50) NOT NULL DEFAULT 'text',
  message_body TEXT NOT NULL,
  meta_message_id VARCHAR(128) NULL,
  queue_reference VARCHAR(120) NULL,
  attachment_name VARCHAR(255) NULL,
  attachment_mime_type VARCHAR(120) NULL,
  attachment_url TEXT NULL,
  request_payload LONGTEXT NOT NULL,
  response_payload LONGTEXT NULL,
  status ENUM('draft', 'queued', 'processing', 'sent', 'failed') NOT NULL DEFAULT 'queued',
  error_details LONGTEXT NULL,
  queued_at DATETIME NULL,
  processed_at DATETIME NULL,
  failed_at DATETIME NULL,
  sent_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_outbound_messages_device_id (device_id),
  KEY idx_outbound_messages_inbound_message_id (inbound_message_id),
  KEY idx_outbound_messages_template_id (template_id),
  KEY idx_outbound_messages_status_created_at (status, created_at),
  KEY idx_outbound_messages_queue_reference (queue_reference),
  CONSTRAINT fk_outbound_messages_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_outbound_messages_inbound_message
    FOREIGN KEY (inbound_message_id) REFERENCES inbound_messages (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
    ,
  CONSTRAINT fk_outbound_messages_template
    FOREIGN KEY (template_id) REFERENCES message_templates (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS autoreply_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  device_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  template_id BIGINT UNSIGNED NULL,
  rule_name VARCHAR(150) NOT NULL,
  match_type ENUM('exact', 'contains', 'begin', 'end') NOT NULL DEFAULT 'contains',
  keyword_value VARCHAR(191) NOT NULL,
  reply_template TEXT NOT NULL,
  priority INT UNSIGNED NOT NULL DEFAULT 100,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  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_autoreply_rules_device_rule_name (device_id, rule_name),
  KEY idx_autoreply_rules_user_id (user_id),
  KEY idx_autoreply_rules_template_id (template_id),
  KEY idx_autoreply_rules_device_status_priority (device_id, status, priority),
  KEY idx_autoreply_rules_match_type (match_type),
  CONSTRAINT fk_autoreply_rules_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_autoreply_rules_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
    ,
  CONSTRAINT fk_autoreply_rules_template
    FOREIGN KEY (template_id) REFERENCES message_templates (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS autoreply_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  autoreply_rule_id BIGINT UNSIGNED NULL,
  device_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  inbound_message_id BIGINT UNSIGNED NULL,
  outbound_message_id BIGINT UNSIGNED NULL,
  rule_name VARCHAR(150) NULL,
  sender_wa_id VARCHAR(64) NOT NULL,
  sender_name VARCHAR(150) NULL,
  inbound_message_body TEXT NULL,
  matched_keyword VARCHAR(191) NULL,
  match_type VARCHAR(50) NOT NULL DEFAULT 'default',
  reply_body TEXT NULL,
  execution_status ENUM('sent', 'failed', 'skipped') NOT NULL DEFAULT 'sent',
  error_details LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_autoreply_logs_rule_id (autoreply_rule_id),
  KEY idx_autoreply_logs_device_created_at (device_id, created_at),
  KEY idx_autoreply_logs_user_created_at (user_id, created_at),
  KEY idx_autoreply_logs_execution_status (execution_status),
  CONSTRAINT fk_autoreply_logs_rule
    FOREIGN KEY (autoreply_rule_id) REFERENCES autoreply_rules (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_autoreply_logs_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_autoreply_logs_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_autoreply_logs_inbound_message
    FOREIGN KEY (inbound_message_id) REFERENCES inbound_messages (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_autoreply_logs_outbound_message
    FOREIGN KEY (outbound_message_id) REFERENCES outbound_messages (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_packages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  package_code VARCHAR(80) NOT NULL,
  package_name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  scope_type ENUM('account', 'device') NOT NULL DEFAULT 'account',
  duration_days INT UNSIGNED NOT NULL DEFAULT 30,
  message_quota INT UNSIGNED NULL,
  device_limit INT UNSIGNED NULL,
  price_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  currency_code VARCHAR(10) NOT NULL DEFAULT 'IDR',
  midtrans_enabled TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  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_billing_packages_code (package_code),
  KEY idx_billing_packages_status_scope (status, scope_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_invoices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  package_id BIGINT UNSIGNED NOT NULL,
  target_device_id BIGINT UNSIGNED NULL,
  invoice_number VARCHAR(60) NOT NULL,
  billing_scope ENUM('account', 'device') NOT NULL DEFAULT 'account',
  invoice_status ENUM('pending', 'paid', 'expired', 'cancelled', 'refunded') NOT NULL DEFAULT 'pending',
  currency_code VARCHAR(10) NOT NULL DEFAULT 'IDR',
  subtotal_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  total_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  duration_days INT UNSIGNED NOT NULL DEFAULT 30,
  message_quota INT UNSIGNED NULL,
  device_limit INT UNSIGNED NULL,
  package_name_snapshot VARCHAR(150) NOT NULL,
  package_code_snapshot VARCHAR(80) NOT NULL,
  expires_at DATETIME NULL,
  paid_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  notes TEXT NULL,
  metadata_json LONGTEXT NULL,
  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_billing_invoices_number (invoice_number),
  KEY idx_billing_invoices_user_status_created (user_id, invoice_status, created_at),
  KEY idx_billing_invoices_package_id (package_id),
  KEY idx_billing_invoices_target_device_id (target_device_id),
  CONSTRAINT fk_billing_invoices_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_billing_invoices_package
    FOREIGN KEY (package_id) REFERENCES billing_packages (id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_billing_invoices_device
    FOREIGN KEY (target_device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  gateway ENUM('midtrans') NOT NULL DEFAULT 'midtrans',
  payment_method ENUM('midtrans_snap') NOT NULL DEFAULT 'midtrans_snap',
  payment_status ENUM('created', 'pending', 'verified', 'failed', 'expired', 'cancelled', 'refunded') NOT NULL DEFAULT 'created',
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  currency_code VARCHAR(10) NOT NULL DEFAULT 'IDR',
  gateway_order_id VARCHAR(120) NOT NULL,
  gateway_transaction_id VARCHAR(120) NULL,
  gateway_payment_type VARCHAR(60) NULL,
  snap_token VARCHAR(120) NULL,
  snap_redirect_url VARCHAR(255) NULL,
  signature_key VARCHAR(255) NULL,
  notification_payload LONGTEXT NULL,
  status_payload LONGTEXT NULL,
  gateway_status VARCHAR(60) NULL,
  transaction_time DATETIME NULL,
  settlement_time DATETIME NULL,
  paid_at DATETIME NULL,
  failed_at DATETIME NULL,
  expires_at DATETIME NULL,
  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_billing_payments_gateway_order_id (gateway_order_id),
  KEY idx_billing_payments_invoice_status (invoice_id, payment_status),
  KEY idx_billing_payments_user_status (user_id, payment_status),
  CONSTRAINT fk_billing_payments_invoice
    FOREIGN KEY (invoice_id) REFERENCES billing_invoices (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_billing_payments_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_subscriptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  package_id BIGINT UNSIGNED NOT NULL,
  source_invoice_id BIGINT UNSIGNED NULL,
  target_device_id BIGINT UNSIGNED NULL,
  billing_scope ENUM('account', 'device') NOT NULL DEFAULT 'account',
  subscription_status ENUM('pending', 'active', 'expired', 'cancelled', 'replaced') NOT NULL DEFAULT 'pending',
  package_name_snapshot VARCHAR(150) NOT NULL,
  package_code_snapshot VARCHAR(80) NOT NULL,
  duration_days INT UNSIGNED NOT NULL DEFAULT 30,
  message_quota_total INT UNSIGNED NULL,
  message_quota_used INT UNSIGNED NOT NULL DEFAULT 0,
  device_limit INT UNSIGNED NULL,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  activated_at DATETIME NULL,
  expired_at DATETIME NULL,
  metadata_json LONGTEXT NULL,
  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_billing_subscriptions_user_status (user_id, subscription_status),
  KEY idx_billing_subscriptions_target_device (target_device_id, subscription_status),
  KEY idx_billing_subscriptions_invoice (source_invoice_id),
  CONSTRAINT fk_billing_subscriptions_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_billing_subscriptions_package
    FOREIGN KEY (package_id) REFERENCES billing_packages (id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  CONSTRAINT fk_billing_subscriptions_invoice
    FOREIGN KEY (source_invoice_id) REFERENCES billing_invoices (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_billing_subscriptions_device
    FOREIGN KEY (target_device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_subscription_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  subscription_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  source_invoice_id BIGINT UNSIGNED NULL,
  event_type ENUM('created', 'activated', 'replaced', 'expired', 'cancelled', 'payment_verified') NOT NULL DEFAULT 'created',
  notes VARCHAR(255) NULL,
  payload_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_billing_subscription_events_subscription (subscription_id, created_at),
  KEY idx_billing_subscription_events_user (user_id, created_at),
  CONSTRAINT fk_billing_subscription_events_subscription
    FOREIGN KEY (subscription_id) REFERENCES billing_subscriptions (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_billing_subscription_events_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_billing_subscription_events_invoice
    FOREIGN KEY (source_invoice_id) REFERENCES billing_invoices (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cs_divisions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  division_name VARCHAR(150) NOT NULL,
  description TEXT NULL,
  routing_priority INT UNSIGNED NOT NULL DEFAULT 100,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  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_cs_divisions_user_name (user_id, division_name),
  KEY idx_cs_divisions_user_status_priority (user_id, status, routing_priority),
  CONSTRAINT fk_cs_divisions_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cs_agents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  division_id BIGINT UNSIGNED NOT NULL,
  agent_name VARCHAR(150) NOT NULL,
  agent_code VARCHAR(80) NOT NULL,
  email VARCHAR(150) NULL,
  routing_weight INT UNSIGNED NOT NULL DEFAULT 1,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  last_assigned_at DATETIME NULL,
  last_seen_at DATETIME NULL,
  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_cs_agents_user_code (user_id, agent_code),
  KEY idx_cs_agents_division_status (division_id, status),
  CONSTRAINT fk_cs_agents_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_cs_agents_division
    FOREIGN KEY (division_id) REFERENCES cs_divisions (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cs_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  division_id BIGINT UNSIGNED NULL,
  template_name VARCHAR(150) NOT NULL,
  template_body TEXT NOT NULL,
  notes TEXT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  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_cs_templates_user_name (user_id, template_name),
  KEY idx_cs_templates_division_status (division_id, status),
  CONSTRAINT fk_cs_templates_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_cs_templates_division
    FOREIGN KEY (division_id) REFERENCES cs_divisions (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cs_conversations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NOT NULL,
  inbound_message_id BIGINT UNSIGNED NULL,
  last_outbound_message_id BIGINT UNSIGNED NULL,
  conversation_wa_id VARCHAR(191) NOT NULL,
  sender_wa_id VARCHAR(191) NOT NULL,
  sender_name VARCHAR(150) NULL,
  division_id BIGINT UNSIGNED NULL,
  assigned_agent_id BIGINT UNSIGNED NULL,
  assignment_mode ENUM('auto', 'manual', 'unassigned') NOT NULL DEFAULT 'unassigned',
  conversation_status ENUM('open', 'pending', 'closed') NOT NULL DEFAULT 'open',
  unread_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_message_at DATETIME NULL,
  last_inbound_at DATETIME NULL,
  last_outbound_at DATETIME NULL,
  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_cs_conversations_device_key (device_id, conversation_wa_id),
  KEY idx_cs_conversations_user_status (user_id, conversation_status),
  KEY idx_cs_conversations_division_agent (division_id, assigned_agent_id, conversation_status),
  CONSTRAINT fk_cs_conversations_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_cs_conversations_device
    FOREIGN KEY (device_id) REFERENCES devices (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_cs_conversations_inbound
    FOREIGN KEY (inbound_message_id) REFERENCES inbound_messages (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_cs_conversations_outbound
    FOREIGN KEY (last_outbound_message_id) REFERENCES outbound_messages (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_cs_conversations_division
    FOREIGN KEY (division_id) REFERENCES cs_divisions (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_cs_conversations_agent
    FOREIGN KEY (assigned_agent_id) REFERENCES cs_agents (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cs_assignment_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  division_id BIGINT UNSIGNED NULL,
  agent_id BIGINT UNSIGNED NULL,
  inbound_message_id BIGINT UNSIGNED NULL,
  event_type ENUM('routed', 'unassigned', 'manual_reassign', 'status_changed') NOT NULL DEFAULT 'routed',
  note VARCHAR(255) NULL,
  metadata_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cs_assignment_events_conversation_created (conversation_id, created_at),
  KEY idx_cs_assignment_events_user_created (user_id, created_at),
  CONSTRAINT fk_cs_assignment_events_conversation
    FOREIGN KEY (conversation_id) REFERENCES cs_conversations (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_cs_assignment_events_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
  CONSTRAINT fk_cs_assignment_events_division
    FOREIGN KEY (division_id) REFERENCES cs_divisions (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_cs_assignment_events_agent
    FOREIGN KEY (agent_id) REFERENCES cs_agents (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL,
  CONSTRAINT fk_cs_assignment_events_inbound
    FOREIGN KEY (inbound_message_id) REFERENCES inbound_messages (id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
