CREATE TABLE IF NOT EXISTS api_credentials (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(80) NOT NULL,
  config_key VARCHAR(120) NOT NULL,
  config_value LONGTEXT NULL,
  is_secret TINYINT(1) NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_provider_key (provider, config_key),
  KEY idx_provider (provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_settings (
  setting_key VARCHAR(120) PRIMARY KEY,
  setting_value TEXT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS quote_searches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source VARCHAR(80) NOT NULL DEFAULT 'manual',
  status VARCHAR(60) NOT NULL DEFAULT 'created',
  providers_json JSON NULL,
  customer_name VARCHAR(160) NULL,
  customer_phone VARCHAR(40) NULL,
  origin VARCHAR(8) NOT NULL,
  destination VARCHAR(8) NOT NULL,
  departure_date DATE NOT NULL,
  return_date DATE NULL,
  adults INT NOT NULL DEFAULT 1,
  children INT NOT NULL DEFAULT 0,
  infants INT NOT NULL DEFAULT 0,
  cabin VARCHAR(40) NOT NULL DEFAULT 'economy',
  currency VARCHAR(8) NOT NULL DEFAULT 'BRL',
  best_cash_price DECIMAL(12,2) NULL,
  best_miles_equivalent DECIMAL(12,2) NULL,
  best_arbitrage_amount DECIMAL(12,2) NULL,
  best_arbitrage_percent DECIMAL(8,2) NULL,
  notes TEXT NULL,
  created_by VARCHAR(120) NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_route_date (origin, destination, departure_date),
  KEY idx_created_at (created_at),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS quote_results (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  quote_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(80) NOT NULL,
  result_type VARCHAR(20) NOT NULL DEFAULT 'cash',
  source_program VARCHAR(120) NULL,
  airline VARCHAR(160) NULL,
  flight_number VARCHAR(80) NULL,
  origin VARCHAR(8) NOT NULL,
  destination VARCHAR(8) NOT NULL,
  departure_at DATETIME NULL,
  arrival_at DATETIME NULL,
  duration_minutes INT NULL,
  stops INT NULL,
  cabin VARCHAR(40) NULL,
  currency VARCHAR(8) NULL,
  total_price DECIMAL(12,2) NULL,
  points_amount BIGINT NULL,
  taxes_amount DECIMAL(12,2) NULL,
  taxes_currency VARCHAR(8) NULL,
  milheiro_value DECIMAL(12,2) NULL,
  equivalent_price DECIMAL(12,2) NULL,
  margin_amount DECIMAL(12,2) NULL,
  margin_percent DECIMAL(8,2) NULL,
  opportunity_status VARCHAR(40) NULL,
  score DECIMAL(8,2) NULL,
  external_id VARCHAR(180) NULL,
  raw_json JSON NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_quote_id (quote_id),
  KEY idx_provider (provider),
  KEY idx_result_type (result_type),
  CONSTRAINT fk_quote_results_quote FOREIGN KEY (quote_id) REFERENCES quote_searches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(80) NOT NULL,
  endpoint VARCHAR(255) NULL,
  method VARCHAR(20) NULL,
  status_code INT NULL,
  ok TINYINT(1) NOT NULL DEFAULT 0,
  request_json JSON NULL,
  response_json JSON NULL,
  error_message TEXT NULL,
  elapsed_ms INT NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_provider_created (provider, created_at),
  KEY idx_ok (ok)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS zdg_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(120) NULL,
  phone VARCHAR(60) NULL,
  message_id VARCHAR(160) NULL,
  payload_json JSON NULL,
  processed TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_phone_created (phone, created_at),
  KEY idx_message_id (message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO app_settings (setting_key, setting_value) VALUES
('default_markup_percent', '12'),
('min_arbitrage_percent', '15'),
('default_currency', 'BRL'),
('milheiro_padrao', '25.00'),
('milheiro_smiles', '20.00'),
('milheiro_latam', '22.00'),
('milheiro_azul', '21.00'),
('milheiro_aadvantage', '32.00')
ON DUPLICATE KEY UPDATE setting_key = setting_key;