CREATE DATABASE IF NOT EXISTS `jobsetu2026`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `jobsetu2026`;

CREATE TABLE IF NOT EXISTS providers (
  phone VARCHAR(20) PRIMARY KEY,
  person_name VARCHAR(150) NOT NULL DEFAULT '',
  firm_name VARCHAR(150) NOT NULL DEFAULT '',
  category VARCHAR(120) NOT NULL DEFAULT '',
  service VARCHAR(120) NOT NULL DEFAULT '',
  sub_services TEXT NULL,
  voter_id_text VARCHAR(80) NOT NULL DEFAULT '',
  approval_status VARCHAR(40) NOT NULL DEFAULT 'PENDING_APPROVAL',
  profile_photo_received ENUM('YES','NO') NOT NULL DEFAULT 'NO',
  profile_photo_media_id VARCHAR(255) NOT NULL DEFAULT '',
  voter_id_photo_received ENUM('YES','NO') NOT NULL DEFAULT 'NO',
  voter_id_media_id VARCHAR(255) NOT NULL DEFAULT '',
  trial_start_date DATETIME NULL,
  trial_end_date DATETIME NULL,
  subscription_status VARCHAR(50) NOT NULL DEFAULT 'PENDING_APPROVAL',
  subscription_plan VARCHAR(50) NOT NULL DEFAULT 'TRIAL',
  subscription_start_date DATETIME NULL,
  subscription_expiry_date DATETIME NULL,
  last_reminder_sent_at DATETIME NULL,
  availability_status VARCHAR(40) NOT NULL DEFAULT 'AVAILABLE',
  lat DECIMAL(10,7) NULL,
  lng DECIMAL(10,7) NULL,
  geohash VARCHAR(20) NULL,
  location_updated_at DATETIME NULL,
  last_assigned_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  approved_at DATETIME NULL,
  notes TEXT NULL,
  INDEX idx_provider_status (approval_status, subscription_status, availability_status),
  INDEX idx_provider_category (category, service)
);

CREATE TABLE IF NOT EXISTS provider_services (
  service_id VARCHAR(80) PRIMARY KEY,
  phone VARCHAR(20) NOT NULL,
  category VARCHAR(120) NOT NULL DEFAULT '',
  service VARCHAR(120) NOT NULL DEFAULT '',
  sub_services TEXT NULL,
  trial_start_date DATETIME NULL,
  trial_end_date DATETIME NULL,
  subscription_status VARCHAR(50) NOT NULL DEFAULT 'PENDING_APPROVAL',
  subscription_plan VARCHAR(50) NOT NULL DEFAULT 'TRIAL',
  subscription_start_date DATETIME NULL,
  subscription_expiry_date DATETIME NULL,
  last_reminder_sent_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  approved_at DATETIME NULL,
  notes TEXT NULL,
  UNIQUE KEY uniq_phone_service (phone, category, service),
  INDEX idx_provider_services_phone (phone),
  INDEX idx_provider_services_match (category, service, subscription_status)
);

CREATE TABLE IF NOT EXISTS seekers (
  phone VARCHAR(20) PRIMARY KEY,
  last_job_id VARCHAR(80) NOT NULL DEFAULT '',
  spam_status VARCHAR(40) NOT NULL DEFAULT 'NORMAL',
  no_response_count_30d INT NOT NULL DEFAULT 0,
  last_location_lat DECIMAL(10,7) NULL,
  last_location_lng DECIMAL(10,7) NULL,
  last_location_geohash VARCHAR(20) NULL,
  last_location_at DATETIME NULL,
  last_request_at DATETIME NULL,
  daily_request_count INT NOT NULL DEFAULT 0,
  last_request_day DATE NULL
);

CREATE TABLE IF NOT EXISTS seeker_profiles (
  phone VARCHAR(20) PRIMARY KEY,
  person_name VARCHAR(150) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS jobs (
  job_id VARCHAR(80) PRIMARY KEY,
  seeker_phone VARCHAR(20) NOT NULL,
  provider_phone VARCHAR(20) NOT NULL DEFAULT '',
  category VARCHAR(120) NOT NULL DEFAULT '',
  service VARCHAR(120) NOT NULL DEFAULT '',
  sub_services TEXT NULL,
  preferred_time VARCHAR(120) NOT NULL DEFAULT '',
  budget VARCHAR(80) NOT NULL DEFAULT '',
  status VARCHAR(60) NOT NULL DEFAULT 'WAITING_SEEKER_LOCATION',
  seeker_lat DECIMAL(10,7) NULL,
  seeker_lng DECIMAL(10,7) NULL,
  provider_lat DECIMAL(10,7) NULL,
  provider_lng DECIMAL(10,7) NULL,
  seeker_geohash VARCHAR(20) NULL,
  provider_geohash VARCHAR(20) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  contact_shared_at DATETIME NULL,
  mutual_accept_deadline DATETIME NULL,
  seeker_response_status VARCHAR(40) NOT NULL DEFAULT '',
  current_provider_phone VARCHAR(20) NOT NULL DEFAULT '',
  current_provider_accepted_at DATETIME NULL,
  attempted_provider_phones TEXT NULL,
  provider_accept_count INT NOT NULL DEFAULT 0,
  request_mode VARCHAR(20) NOT NULL DEFAULT 'REGULAR',
  payment_status VARCHAR(40) NOT NULL DEFAULT 'NOT_REQUIRED',
  provider_response_deadline DATETIME NULL,
  INDEX idx_jobs_seeker (seeker_phone, status),
  INDEX idx_jobs_provider (provider_phone, status),
  INDEX idx_jobs_current_provider (current_provider_phone, status)
);

CREATE TABLE IF NOT EXISTS jobs_archive (
  job_id VARCHAR(80) PRIMARY KEY,
  seeker_phone VARCHAR(20) NOT NULL,
  provider_phone VARCHAR(20) NOT NULL DEFAULT '',
  category VARCHAR(120) NOT NULL DEFAULT '',
  service VARCHAR(120) NOT NULL DEFAULT '',
  sub_services TEXT NULL,
  preferred_time VARCHAR(120) NOT NULL DEFAULT '',
  budget VARCHAR(80) NOT NULL DEFAULT '',
  status VARCHAR(60) NOT NULL DEFAULT '',
  seeker_lat DECIMAL(10,7) NULL,
  seeker_lng DECIMAL(10,7) NULL,
  provider_lat DECIMAL(10,7) NULL,
  provider_lng DECIMAL(10,7) NULL,
  seeker_geohash VARCHAR(20) NULL,
  provider_geohash VARCHAR(20) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  contact_shared_at DATETIME NULL,
  mutual_accept_deadline DATETIME NULL,
  seeker_response_status VARCHAR(40) NOT NULL DEFAULT '',
  current_provider_phone VARCHAR(20) NOT NULL DEFAULT '',
  current_provider_accepted_at DATETIME NULL,
  attempted_provider_phones TEXT NULL,
  provider_accept_count INT NOT NULL DEFAULT 0,
  request_mode VARCHAR(20) NOT NULL DEFAULT 'REGULAR',
  payment_status VARCHAR(40) NOT NULL DEFAULT 'NOT_REQUIRED',
  provider_response_deadline DATETIME NULL,
  archived_at DATETIME NOT NULL,
  INDEX idx_jobs_archive_seeker (seeker_phone, status),
  INDEX idx_jobs_archive_provider (provider_phone, status),
  INDEX idx_jobs_archive_updated (updated_at),
  INDEX idx_jobs_archive_archived (archived_at)
);

CREATE TABLE IF NOT EXISTS user_states (
  phone VARCHAR(20) PRIMARY KEY,
  role VARCHAR(20) NOT NULL DEFAULT '',
  state VARCHAR(60) NOT NULL DEFAULT '',
  reference_id VARCHAR(80) NOT NULL DEFAULT '',
  updated_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS message_log (
  message_id VARCHAR(120) PRIMARY KEY,
  phone VARCHAR(20) NOT NULL DEFAULT '',
  type VARCHAR(40) NOT NULL DEFAULT '',
  logged_at DATETIME NOT NULL,
  INDEX idx_message_phone (phone)
);

CREATE TABLE IF NOT EXISTS message_log_archive (
  message_id VARCHAR(120) PRIMARY KEY,
  phone VARCHAR(20) NOT NULL DEFAULT '',
  type VARCHAR(40) NOT NULL DEFAULT '',
  logged_at DATETIME NOT NULL,
  archived_at DATETIME NOT NULL,
  INDEX idx_message_archive_phone (phone),
  INDEX idx_message_archive_logged (logged_at)
);

CREATE TABLE IF NOT EXISTS whatsapp_messages (
  message_id VARCHAR(120) PRIMARY KEY,
  phone VARCHAR(20) NOT NULL DEFAULT '',
  direction VARCHAR(20) NOT NULL DEFAULT 'OUTBOUND',
  wa_type VARCHAR(40) NOT NULL DEFAULT 'text',
  body_text TEXT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'sent',
  template_name VARCHAR(120) NOT NULL DEFAULT '',
  payload_json LONGTEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_whatsapp_phone_time (phone, created_at),
  INDEX idx_whatsapp_direction_time (direction, created_at)
);

CREATE TABLE IF NOT EXISTS marketing_campaigns (
  campaign_id VARCHAR(80) PRIMARY KEY,
  campaign_name VARCHAR(150) NOT NULL DEFAULT '',
  template_name VARCHAR(120) NOT NULL DEFAULT '',
  template_category VARCHAR(40) NOT NULL DEFAULT 'MARKETING',
  language_code VARCHAR(20) NOT NULL DEFAULT 'en',
  template_payload_json LONGTEXT NULL,
  message_preview TEXT NULL,
  recipient_count INT NOT NULL DEFAULT 0,
  scheduled_at DATETIME NULL,
  created_by VARCHAR(20) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  INDEX idx_marketing_campaigns_created (created_at)
);

CREATE TABLE IF NOT EXISTS marketing_messages (
  record_id VARCHAR(80) PRIMARY KEY,
  campaign_id VARCHAR(80) NOT NULL DEFAULT '',
  wa_message_id VARCHAR(120) NOT NULL DEFAULT '',
  phone VARCHAR(20) NOT NULL DEFAULT '',
  direction VARCHAR(20) NOT NULL DEFAULT 'OUTBOUND',
  message_kind VARCHAR(40) NOT NULL DEFAULT 'campaign_template',
  template_name VARCHAR(120) NOT NULL DEFAULT '',
  message_text TEXT NULL,
  delivery_status VARCHAR(40) NOT NULL DEFAULT 'PENDING',
  delivery_at DATETIME NULL,
  read_status VARCHAR(40) NOT NULL DEFAULT 'NOT_READ',
  read_at DATETIME NULL,
  reply_status VARCHAR(40) NOT NULL DEFAULT 'NO_REPLY',
  reply_at DATETIME NULL,
  wa_status VARCHAR(40) NOT NULL DEFAULT '',
  attempt_count INT NOT NULL DEFAULT 0,
  last_attempt_at DATETIME NULL,
  last_error TEXT NULL,
  context_json LONGTEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_marketing_messages_campaign (campaign_id, created_at),
  INDEX idx_marketing_messages_phone (phone, created_at),
  INDEX idx_marketing_messages_wa_id (wa_message_id),
  INDEX idx_marketing_messages_retry (campaign_id, delivery_status, attempt_count, created_at)
);

CREATE TABLE IF NOT EXISTS spam_events (
  event_id VARCHAR(80) PRIMARY KEY,
  phone VARCHAR(20) NOT NULL DEFAULT '',
  event_type VARCHAR(60) NOT NULL DEFAULT '',
  details TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_spam_phone_type_time (phone, event_type, created_at)
);

CREATE TABLE IF NOT EXISTS spam_events_archive (
  event_id VARCHAR(80) PRIMARY KEY,
  phone VARCHAR(20) NOT NULL DEFAULT '',
  event_type VARCHAR(60) NOT NULL DEFAULT '',
  details TEXT NULL,
  created_at DATETIME NOT NULL,
  archived_at DATETIME NOT NULL,
  INDEX idx_spam_archive_phone_type_time (phone, event_type, created_at),
  INDEX idx_spam_archive_archived (archived_at)
);

CREATE TABLE IF NOT EXISTS blocked_numbers (
  phone VARCHAR(20) PRIMARY KEY,
  block_status VARCHAR(40) NOT NULL DEFAULT 'NORMAL',
  reason TEXT NULL,
  blocked_until DATETIME NULL,
  created_at DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS admin_actions (
  action_id VARCHAR(80) PRIMARY KEY,
  admin_phone VARCHAR(20) NOT NULL DEFAULT '',
  command VARCHAR(60) NOT NULL DEFAULT '',
  target_phone VARCHAR(20) NOT NULL DEFAULT '',
  details TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_admin_target (target_phone, created_at)
);

CREATE TABLE IF NOT EXISTS hourly_reports (
  report_id VARCHAR(80) PRIMARY KEY,
  admin_phone VARCHAR(20) NOT NULL DEFAULT '',
  report_key VARCHAR(20) NOT NULL DEFAULT '',
  report_message TEXT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uniq_hourly_report_key (report_key),
  INDEX idx_hourly_admin_created (admin_phone, created_at)
);

CREATE TABLE IF NOT EXISTS ratings (
  rating_id VARCHAR(80) PRIMARY KEY,
  job_id VARCHAR(80) NOT NULL,
  from_phone VARCHAR(20) NOT NULL DEFAULT '',
  to_phone VARCHAR(20) NOT NULL DEFAULT '',
  rating INT NOT NULL DEFAULT 0,
  feedback_text TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_ratings_job (job_id),
  INDEX idx_ratings_target (to_phone)
);

CREATE TABLE IF NOT EXISTS dispatch_log (
  dispatch_id VARCHAR(80) PRIMARY KEY,
  job_id VARCHAR(80) NOT NULL,
  provider_phone VARCHAR(20) NOT NULL DEFAULT '',
  action VARCHAR(60) NOT NULL DEFAULT '',
  details TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_dispatch_job (job_id, created_at),
  INDEX idx_dispatch_provider (provider_phone, created_at)
);

CREATE TABLE IF NOT EXISTS dispatch_log_archive (
  dispatch_id VARCHAR(80) PRIMARY KEY,
  job_id VARCHAR(80) NOT NULL,
  provider_phone VARCHAR(20) NOT NULL DEFAULT '',
  action VARCHAR(60) NOT NULL DEFAULT '',
  details TEXT NULL,
  created_at DATETIME NOT NULL,
  archived_at DATETIME NOT NULL,
  INDEX idx_dispatch_archive_job (job_id, created_at),
  INDEX idx_dispatch_archive_provider (provider_phone, created_at),
  INDEX idx_dispatch_archive_archived (archived_at)
);

CREATE TABLE IF NOT EXISTS payment_requests (
  payment_id VARCHAR(80) PRIMARY KEY,
  phone VARCHAR(20) NOT NULL DEFAULT '',
  payment_type VARCHAR(40) NOT NULL DEFAULT '',
  reference_id VARCHAR(80) NOT NULL DEFAULT '',
  amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  status VARCHAR(40) NOT NULL DEFAULT '',
  payment_link TEXT NULL,
  payment_reference VARCHAR(120) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  confirmed_at DATETIME NULL,
  INDEX idx_payment_phone (phone, payment_type),
  INDEX idx_payment_reference (reference_id, payment_type)
);

CREATE TABLE IF NOT EXISTS deleted_users (
  archive_id VARCHAR(80) PRIMARY KEY,
  phone VARCHAR(20) NOT NULL,
  user_types VARCHAR(80) NOT NULL DEFAULT '',
  person_name VARCHAR(150) NOT NULL DEFAULT '',
  firm_name VARCHAR(150) NOT NULL DEFAULT '',
  category VARCHAR(120) NOT NULL DEFAULT '',
  service VARCHAR(120) NOT NULL DEFAULT '',
  sub_services TEXT NULL,
  voter_id_text VARCHAR(80) NOT NULL DEFAULT '',
  approval_status VARCHAR(40) NOT NULL DEFAULT '',
  subscription_status VARCHAR(50) NOT NULL DEFAULT '',
  subscription_plan VARCHAR(50) NOT NULL DEFAULT '',
  provider_snapshot LONGTEXT NULL,
  seeker_snapshot LONGTEXT NULL,
  provider_services_snapshot LONGTEXT NULL,
  deleted_at DATETIME NOT NULL,
  deleted_reason TEXT NULL,
  rejoin_fee_due DECIMAL(10,2) NOT NULL DEFAULT 1000,
  INDEX idx_deleted_user_phone (phone, deleted_at)
);