-- Wezapp Shots - initial core schema
-- Scope: Wezapp project only
-- App: Shots

CREATE TABLE IF NOT EXISTS wezapp_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(160) NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(30) NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'operator',
  status VARCHAR(30) NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_wezapp_users_email (email),
  KEY idx_wezapp_users_status (status),
  KEY idx_wezapp_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_clients (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(190) NOT NULL,
  client_type VARCHAR(60) NOT NULL DEFAULT 'other',
  document VARCHAR(40) NULL,
  responsible_name VARCHAR(160) NULL,
  phone VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'active',
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_wezapp_clients_name (name),
  KEY idx_wezapp_clients_type (client_type),
  KEY idx_wezapp_clients_status (status),
  KEY idx_wezapp_clients_document (document)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_campaigns (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  description TEXT NULL,
  campaign_type VARCHAR(60) NOT NULL DEFAULT 'other',
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  internal_notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_wezapp_campaigns_client (client_id),
  KEY idx_wezapp_campaigns_status (status),
  KEY idx_wezapp_campaigns_type (campaign_type),
  KEY idx_wezapp_campaigns_period (starts_at, ends_at),
  CONSTRAINT fk_wezapp_campaigns_client FOREIGN KEY (client_id) REFERENCES wezapp_clients (id),
  CONSTRAINT fk_wezapp_campaigns_created_by FOREIGN KEY (created_by) REFERENCES wezapp_users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_shot_packages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NULL,
  quantity INT UNSIGNED NOT NULL,
  unit_price DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  total_amount DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  purchase_date DATE NOT NULL,
  payment_method VARCHAR(60) NULL,
  financial_status VARCHAR(40) NOT NULL DEFAULT 'pending',
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_wezapp_shot_packages_client (client_id),
  KEY idx_wezapp_shot_packages_campaign (campaign_id),
  KEY idx_wezapp_shot_packages_financial_status (financial_status),
  KEY idx_wezapp_shot_packages_purchase_date (purchase_date),
  CONSTRAINT fk_wezapp_shot_packages_client FOREIGN KEY (client_id) REFERENCES wezapp_clients (id),
  CONSTRAINT fk_wezapp_shot_packages_campaign FOREIGN KEY (campaign_id) REFERENCES wezapp_campaigns (id),
  CONSTRAINT fk_wezapp_shot_packages_created_by FOREIGN KEY (created_by) REFERENCES wezapp_users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_shot_ledger (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NULL,
  package_id BIGINT UNSIGNED NULL,
  movement_type VARCHAR(40) NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(12,4) NULL,
  amount DECIMAL(12,4) NULL,
  reference_type VARCHAR(80) NULL,
  reference_id BIGINT UNSIGNED NULL,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_wezapp_shot_ledger_client (client_id),
  KEY idx_wezapp_shot_ledger_campaign (campaign_id),
  KEY idx_wezapp_shot_ledger_package (package_id),
  KEY idx_wezapp_shot_ledger_movement (movement_type),
  KEY idx_wezapp_shot_ledger_reference (reference_type, reference_id),
  CONSTRAINT fk_wezapp_shot_ledger_client FOREIGN KEY (client_id) REFERENCES wezapp_clients (id),
  CONSTRAINT fk_wezapp_shot_ledger_campaign FOREIGN KEY (campaign_id) REFERENCES wezapp_campaigns (id),
  CONSTRAINT fk_wezapp_shot_ledger_package FOREIGN KEY (package_id) REFERENCES wezapp_shot_packages (id),
  CONSTRAINT fk_wezapp_shot_ledger_created_by FOREIGN KEY (created_by) REFERENCES wezapp_users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_channel_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(160) NOT NULL,
  description TEXT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_wezapp_channel_groups_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_channels (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  channel_group_id BIGINT UNSIGNED NULL,
  name VARCHAR(160) NOT NULL,
  provider VARCHAR(80) NOT NULL DEFAULT 'oratrix',
  external_channel_id VARCHAR(120) NULL,
  phone_number VARCHAR(30) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'active',
  daily_limit INT UNSIGNED NULL,
  weekly_limit INT UNSIGNED NULL,
  priority INT UNSIGNED NOT NULL DEFAULT 100,
  warmup_status VARCHAR(40) NULL,
  last_error_at DATETIME NULL,
  last_error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_wezapp_channels_group (channel_group_id),
  KEY idx_wezapp_channels_provider (provider),
  KEY idx_wezapp_channels_status (status),
  KEY idx_wezapp_channels_external (external_channel_id),
  CONSTRAINT fk_wezapp_channels_group FOREIGN KEY (channel_group_id) REFERENCES wezapp_channel_groups (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_contact_bases (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NULL,
  name VARCHAR(190) NOT NULL,
  source VARCHAR(120) NULL,
  total_contacts INT UNSIGNED NOT NULL DEFAULT 0,
  valid_contacts INT UNSIGNED NOT NULL DEFAULT 0,
  invalid_contacts INT UNSIGNED NOT NULL DEFAULT 0,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  imported_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_wezapp_contact_bases_client (client_id),
  KEY idx_wezapp_contact_bases_campaign (campaign_id),
  KEY idx_wezapp_contact_bases_status (status),
  CONSTRAINT fk_wezapp_contact_bases_client FOREIGN KEY (client_id) REFERENCES wezapp_clients (id),
  CONSTRAINT fk_wezapp_contact_bases_campaign FOREIGN KEY (campaign_id) REFERENCES wezapp_campaigns (id),
  CONSTRAINT fk_wezapp_contact_bases_imported_by FOREIGN KEY (imported_by) REFERENCES wezapp_users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_contacts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  base_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NULL,
  phone VARCHAR(30) NOT NULL,
  email VARCHAR(190) NULL,
  document VARCHAR(40) NULL,
  metadata_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'valid',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_wezapp_contacts_base (base_id),
  KEY idx_wezapp_contacts_phone (phone),
  KEY idx_wezapp_contacts_status (status),
  CONSTRAINT fk_wezapp_contacts_base FOREIGN KEY (base_id) REFERENCES wezapp_contact_bases (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_dispatch_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  base_id BIGINT UNSIGNED NULL,
  channel_group_id BIGINT UNSIGNED NULL,
  name VARCHAR(190) NOT NULL,
  message_template VARCHAR(190) NULL,
  message_payload_json JSON NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'draft',
  total_items INT UNSIGNED NOT NULL DEFAULT 0,
  sent_items INT UNSIGNED NOT NULL DEFAULT 0,
  failed_items INT UNSIGNED NOT NULL DEFAULT 0,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_wezapp_dispatch_jobs_client (client_id),
  KEY idx_wezapp_dispatch_jobs_campaign (campaign_id),
  KEY idx_wezapp_dispatch_jobs_base (base_id),
  KEY idx_wezapp_dispatch_jobs_status (status),
  CONSTRAINT fk_wezapp_dispatch_jobs_client FOREIGN KEY (client_id) REFERENCES wezapp_clients (id),
  CONSTRAINT fk_wezapp_dispatch_jobs_campaign FOREIGN KEY (campaign_id) REFERENCES wezapp_campaigns (id),
  CONSTRAINT fk_wezapp_dispatch_jobs_base FOREIGN KEY (base_id) REFERENCES wezapp_contact_bases (id),
  CONSTRAINT fk_wezapp_dispatch_jobs_channel_group FOREIGN KEY (channel_group_id) REFERENCES wezapp_channel_groups (id),
  CONSTRAINT fk_wezapp_dispatch_jobs_created_by FOREIGN KEY (created_by) REFERENCES wezapp_users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_dispatch_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  dispatch_job_id BIGINT UNSIGNED NOT NULL,
  contact_id BIGINT UNSIGNED NOT NULL,
  channel_id BIGINT UNSIGNED NULL,
  shot_ledger_id BIGINT UNSIGNED NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'pending',
  scheduled_at DATETIME NULL,
  sent_at DATETIME NULL,
  delivered_at DATETIME NULL,
  read_at DATETIME NULL,
  responded_at DATETIME NULL,
  failed_at DATETIME NULL,
  error_message TEXT NULL,
  external_message_id VARCHAR(190) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_wezapp_dispatch_items_job (dispatch_job_id),
  KEY idx_wezapp_dispatch_items_contact (contact_id),
  KEY idx_wezapp_dispatch_items_channel (channel_id),
  KEY idx_wezapp_dispatch_items_status (status),
  KEY idx_wezapp_dispatch_items_external (external_message_id),
  CONSTRAINT fk_wezapp_dispatch_items_job FOREIGN KEY (dispatch_job_id) REFERENCES wezapp_dispatch_jobs (id),
  CONSTRAINT fk_wezapp_dispatch_items_contact FOREIGN KEY (contact_id) REFERENCES wezapp_contacts (id),
  CONSTRAINT fk_wezapp_dispatch_items_channel FOREIGN KEY (channel_id) REFERENCES wezapp_channels (id),
  CONSTRAINT fk_wezapp_dispatch_items_ledger FOREIGN KEY (shot_ledger_id) REFERENCES wezapp_shot_ledger (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_dispatch_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  dispatch_job_id BIGINT UNSIGNED NULL,
  dispatch_item_id BIGINT UNSIGNED NULL,
  channel_id BIGINT UNSIGNED NULL,
  provider VARCHAR(80) NULL,
  request_payload_json JSON NULL,
  response_payload_json JSON NULL,
  http_status INT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'logged',
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_wezapp_dispatch_logs_job (dispatch_job_id),
  KEY idx_wezapp_dispatch_logs_item (dispatch_item_id),
  KEY idx_wezapp_dispatch_logs_channel (channel_id),
  KEY idx_wezapp_dispatch_logs_status (status),
  CONSTRAINT fk_wezapp_dispatch_logs_job FOREIGN KEY (dispatch_job_id) REFERENCES wezapp_dispatch_jobs (id),
  CONSTRAINT fk_wezapp_dispatch_logs_item FOREIGN KEY (dispatch_item_id) REFERENCES wezapp_dispatch_items (id),
  CONSTRAINT fk_wezapp_dispatch_logs_channel FOREIGN KEY (channel_id) REFERENCES wezapp_channels (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wezapp_audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(120) NULL,
  entity_id BIGINT UNSIGNED NULL,
  before_json JSON NULL,
  after_json JSON NULL,
  ip_address VARCHAR(60) NULL,
  user_agent TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_wezapp_audit_logs_user (user_id),
  KEY idx_wezapp_audit_logs_action (action),
  KEY idx_wezapp_audit_logs_entity (entity_type, entity_id),
  CONSTRAINT fk_wezapp_audit_logs_user FOREIGN KEY (user_id) REFERENCES wezapp_users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;