SET NAMES utf8mb4;


CREATE TABLE IF NOT EXISTS wa_auth_creds (
  session_id VARCHAR(80) PRIMARY KEY,
  creds_data LONGTEXT NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_auth_keys (
  session_id VARCHAR(80) NOT NULL,
  category VARCHAR(100) NOT NULL,
  key_id VARCHAR(255) NOT NULL,
  key_data LONGTEXT NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (session_id, category, key_id),
  INDEX idx_auth_keys_session (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_settings (
  setting_key VARCHAR(120) PRIMARY KEY,
  setting_value LONGTEXT NULL,
  is_encrypted TINYINT(1) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_admin_numbers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(32) NOT NULL UNIQUE,
  display_name VARCHAR(120) NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_groups (
  jid VARCHAR(100) PRIMARY KEY,
  group_name VARCHAR(255) NOT NULL,
  group_type ENUM('client','provider') NOT NULL,
  linked_user_id BIGINT NULL,
  linked_username VARCHAR(190) NULL,
  provider_key VARCHAR(255) NULL,
  language ENUM('ar','en') NOT NULL DEFAULT 'ar',
  status ENUM('active','pending_language','disabled') NOT NULL DEFAULT 'active',
  linked_by VARCHAR(100) NULL,
  metadata LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_groups_type_status (group_type, status),
  INDEX idx_groups_username (linked_username),
  INDEX idx_groups_provider (provider_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_provider_aliases (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  alias_key VARCHAR(255) NOT NULL UNIQUE,
  group_jid VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_provider_alias_group FOREIGN KEY (group_jid) REFERENCES wa_groups(jid) ON DELETE CASCADE,
  INDEX idx_provider_alias_group (group_jid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_templates (
  template_key VARCHAR(120) PRIMARY KEY,
  description VARCHAR(255) NULL,
  ar_text LONGTEXT NOT NULL,
  en_text LONGTEXT NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  whatsapp_message_id VARCHAR(190) NOT NULL UNIQUE,
  group_jid VARCHAR(100) NOT NULL,
  sender_jid VARCHAR(100) NOT NULL,
  linked_username VARCHAR(190) NULL,
  request_type ENUM('speedup','cancel','refill','partial') NOT NULL,
  language ENUM('ar','en') NOT NULL DEFAULT 'ar',
  raw_text LONGTEXT NULL,
  status ENUM('received','processed','partially_processed','failed') NOT NULL DEFAULT 'received',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_requests_created (created_at),
  INDEX idx_requests_type_status (request_type, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_request_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  request_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT NOT NULL,
  order_user VARCHAR(190) NULL,
  provider_key VARCHAR(255) NULL,
  provider_group_jid VARCHAR(100) NULL,
  service_id BIGINT NULL,
  service_name VARCHAR(500) NULL,
  order_status VARCHAR(60) NULL,
  result ENUM('accepted','rejected','missing') NOT NULL,
  reason_key VARCHAR(120) NULL,
  reason_text VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_request_item_request FOREIGN KEY (request_id) REFERENCES wa_requests(id) ON DELETE CASCADE,
  INDEX idx_request_items_request (request_id),
  INDEX idx_request_items_order (order_id),
  INDEX idx_request_items_provider (provider_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_partial_watch (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  client_group_jid VARCHAR(100) NOT NULL,
  provider_group_jid VARCHAR(100) NULL,
  linked_username VARCHAR(190) NULL,
  provider_key VARCHAR(255) NULL,
  language ENUM('ar','en') NOT NULL DEFAULT 'ar',
  status ENUM('active','resolved','stopped') NOT NULL DEFAULT 'active',
  last_order_status VARCHAR(60) NULL,
  reminder_count INT UNSIGNED NOT NULL DEFAULT 0,
  next_check_at DATETIME NOT NULL,
  last_checked_at DATETIME NULL,
  resolved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_partial_order_client (order_id, client_group_jid),
  INDEX idx_partial_due (status, next_check_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_processed_messages (
  whatsapp_message_id VARCHAR(190) PRIMARY KEY,
  chat_jid VARCHAR(100) NOT NULL,
  processed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_processed_at (processed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  level ENUM('debug','info','warning','error') NOT NULL DEFAULT 'info',
  event_type VARCHAR(120) NOT NULL,
  message TEXT NOT NULL,
  context LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_logs_created (created_at),
  INDEX idx_logs_event (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_user_bindings (
  chat_jid VARCHAR(100) PRIMARY KEY,
  phone VARCHAR(32) NOT NULL,
  linked_user_id BIGINT NULL,
  linked_username VARCHAR(190) NOT NULL,
  language ENUM('ar','en') NOT NULL DEFAULT 'ar',
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_bindings_phone (phone),
  INDEX idx_user_bindings_username (linked_username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_conversation_states (
  chat_jid VARCHAR(100) PRIMARY KEY,
  sender_phone VARCHAR(32) NOT NULL,
  state_type VARCHAR(80) NOT NULL,
  payload LONGTEXT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_conversation_state_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_private_contacts (
  chat_jid VARCHAR(100) PRIMARY KEY,
  sender_phone VARCHAR(32) NOT NULL,
  welcome_sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_private_contacts_phone (sender_phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wa_cancel_watch (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  client_chat_jid VARCHAR(100) NOT NULL,
  provider_group_jid VARCHAR(100) NULL,
  linked_username VARCHAR(190) NULL,
  provider_key VARCHAR(255) NULL,
  provider_order_id VARCHAR(190) NULL,
  language ENUM('ar','en') NOT NULL DEFAULT 'ar',
  status ENUM('active','resolved','stopped') NOT NULL DEFAULT 'active',
  last_order_status VARCHAR(60) NULL,
  reminder_count INT UNSIGNED NOT NULL DEFAULT 0,
  next_check_at DATETIME NOT NULL,
  last_checked_at DATETIME NULL,
  resolved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cancel_order_client (order_id, client_chat_jid),
  INDEX idx_cancel_due (status, next_check_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;