-- DW Ads Reporter full install SQL
-- Generated for the full package installer.
-- This file is safe to import on an empty MySQL/MariaDB database.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `remember_token` VARCHAR(100) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `email` VARCHAR(255) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `failed_jobs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` VARCHAR(255) NOT NULL,
  `connection` TEXT NOT NULL,
  `queue` TEXT NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `exception` LONGTEXT NOT NULL,
  `failed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `personal_access_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tokenable_type` VARCHAR(255) NOT NULL,
  `tokenable_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `abilities` TEXT NULL,
  `last_used_at` TIMESTAMP NULL DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`, `tokenable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `clients` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_name` VARCHAR(255) NOT NULL,
  `contact_person` VARCHAR(255) NULL,
  `email` VARCHAR(255) NULL,
  `phone` VARCHAR(255) NULL,
  `website` VARCHAR(255) NULL,
  `sector` VARCHAR(255) NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT 'active',
  `onboarding_status` VARCHAR(30) NOT NULL DEFAULT 'not_started',
  `onboarding_step` VARCHAR(50) NULL,
  `onboarding_completed_at` TIMESTAMP NULL DEFAULT NULL,
  `onboarding_meta` JSON NULL,
  `currency_code` VARCHAR(3) NOT NULL DEFAULT 'TRY',
  `timezone` VARCHAR(255) NOT NULL DEFAULT 'Europe/Istanbul',
  `report_language` VARCHAR(5) NOT NULL DEFAULT 'tr',
  `logo_path` VARCHAR(255) NULL,
  `primary_color` VARCHAR(20) NULL,
  `report_recipients` JSON NULL,
  `portal_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `portal_welcome_text` TEXT NULL,
  `notes` TEXT NULL,
  `internal_notes` TEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clients_status_company_name_index` (`status`, `company_name`),
  KEY `clients_created_by_foreign` (`created_by`),
  CONSTRAINT `clients_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `client_user` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `role` VARCHAR(255) NOT NULL DEFAULT 'viewer',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `permissions` JSON NULL,
  `last_portal_login_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `client_user_client_id_user_id_unique` (`client_id`, `user_id`),
  KEY `client_user_user_id_foreign` (`user_id`),
  CONSTRAINT `client_user_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `client_user_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `secure_credentials` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NULL,
  `platform` VARCHAR(40) NOT NULL,
  `credential_type` VARCHAR(60) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `encrypted_payload` LONGTEXT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `last_checked_at` TIMESTAMP NULL DEFAULT NULL,
  `last_error` TEXT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `secure_credentials_platform_credential_type_is_active_index` (`platform`, `credential_type`, `is_active`),
  KEY `secure_credentials_client_id_foreign` (`client_id`),
  KEY `secure_credentials_created_by_foreign` (`created_by`),
  KEY `secure_credentials_updated_by_foreign` (`updated_by`),
  CONSTRAINT `secure_credentials_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `secure_credentials_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `secure_credentials_updated_by_foreign` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `platform_connections` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `platform` VARCHAR(40) NOT NULL,
  `connection_name` VARCHAR(255) NOT NULL,
  `account_id` VARCHAR(255) NULL,
  `account_name` VARCHAR(255) NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT 'draft',
  `currency_code` VARCHAR(3) NULL,
  `timezone` VARCHAR(255) NULL,
  `last_sync_at` TIMESTAMP NULL DEFAULT NULL,
  `last_error` TEXT NULL,
  `meta` JSON NULL,
  `secure_credential_id` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `platform_connections_client_id_platform_status_index` (`client_id`, `platform`, `status`),
  KEY `platform_connections_secure_credential_id_foreign` (`secure_credential_id`),
  CONSTRAINT `platform_connections_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `platform_connections_secure_credential_id_foreign` FOREIGN KEY (`secure_credential_id`) REFERENCES `secure_credentials` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `brand_kits` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `agency_name` VARCHAR(255) NULL,
  `agency_logo_url` VARCHAR(500) NULL,
  `client_logo_url` VARCHAR(500) NULL,
  `primary_color` VARCHAR(20) NOT NULL DEFAULT '#111827',
  `accent_color` VARCHAR(20) NOT NULL DEFAULT '#ef5f86',
  `secondary_color` VARCHAR(20) NULL,
  `text_color` VARCHAR(20) NULL,
  `muted_color` VARCHAR(20) NULL,
  `cover_style` VARCHAR(40) NOT NULL DEFAULT 'gradient',
  `cover_background_url` VARCHAR(500) NULL,
  `footer_note` VARCHAR(600) NULL,
  `contact_email` VARCHAR(255) NULL,
  `contact_phone` VARCHAR(80) NULL,
  `website_url` VARCHAR(220) NULL,
  `settings` JSON NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 100,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `brand_kits_client_id_is_default_is_active_index` (`client_id`, `is_default`, `is_active`),
  CONSTRAINT `brand_kits_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `report_templates` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NULL,
  `brand_kit_id` BIGINT UNSIGNED NULL,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `type` VARCHAR(40) NOT NULL DEFAULT 'monthly',
  `channels` JSON NULL,
  `sections` JSON NULL,
  `settings` JSON NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 100,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `report_templates_client_id_foreign` (`client_id`),
  KEY `report_templates_brand_kit_id_foreign` (`brand_kit_id`),
  CONSTRAINT `report_templates_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `report_templates_brand_kit_id_foreign` FOREIGN KEY (`brand_kit_id`) REFERENCES `brand_kits` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `report_schedules` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `report_template_id` BIGINT UNSIGNED NULL,
  `frequency` VARCHAR(30) NOT NULL DEFAULT 'monthly',
  `daily_time` TIME NULL,
  `weekly_day` TINYINT UNSIGNED NULL,
  `monthly_day` TINYINT UNSIGNED NULL,
  `timezone` VARCHAR(255) NOT NULL DEFAULT 'Europe/Istanbul',
  `channels` JSON NULL,
  `recipients` JSON NULL,
  `cc` JSON NULL,
  `bcc` JSON NULL,
  `include_pdf` TINYINT(1) NOT NULL DEFAULT 1,
  `include_excel` TINYINT(1) NOT NULL DEFAULT 0,
  `include_csv` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_run_at` TIMESTAMP NULL DEFAULT NULL,
  `next_run_at` TIMESTAMP NULL DEFAULT NULL,
  `last_status` VARCHAR(255) NULL,
  `last_error` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `report_schedules_client_id_frequency_is_active_index` (`client_id`, `frequency`, `is_active`),
  KEY `report_schedules_report_template_id_foreign` (`report_template_id`),
  CONSTRAINT `report_schedules_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `report_schedules_report_template_id_foreign` FOREIGN KEY (`report_template_id`) REFERENCES `report_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `generated_reports` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `report_template_id` BIGINT UNSIGNED NULL,
  `report_schedule_id` BIGINT UNSIGNED NULL,
  `report_type` VARCHAR(40) NOT NULL,
  `channels` JSON NULL,
  `date_from` DATE NOT NULL,
  `date_to` DATE NOT NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT 'draft',
  `summary` JSON NULL,
  `ai_status` VARCHAR(255) NOT NULL DEFAULT 'pending',
  `ai_provider` VARCHAR(255) NULL,
  `ai_executive_summary` LONGTEXT NULL,
  `ai_customer_note` LONGTEXT NULL,
  `ai_technical_note` LONGTEXT NULL,
  `ai_risks` JSON NULL,
  `ai_opportunities` JSON NULL,
  `ai_actions` JSON NULL,
  `ai_raw_response` LONGTEXT NULL,
  `ai_error_message` TEXT NULL,
  `ai_generated_at` TIMESTAMP NULL DEFAULT NULL,
  `html_path` VARCHAR(255) NULL,
  `pdf_path` VARCHAR(255) NULL,
  `excel_path` VARCHAR(255) NULL,
  `csv_path` VARCHAR(255) NULL,
  `sent_at` TIMESTAMP NULL DEFAULT NULL,
  `generated_at` TIMESTAMP NULL DEFAULT NULL,
  `generated_by` BIGINT UNSIGNED NULL,
  `error_message` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `generated_reports_client_id_report_type_date_from_date_to_index` (`client_id`, `report_type`, `date_from`, `date_to`),
  KEY `generated_reports_report_template_id_foreign` (`report_template_id`),
  KEY `generated_reports_report_schedule_id_foreign` (`report_schedule_id`),
  KEY `generated_reports_generated_by_foreign` (`generated_by`),
  CONSTRAINT `generated_reports_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `generated_reports_report_template_id_foreign` FOREIGN KEY (`report_template_id`) REFERENCES `report_templates` (`id`) ON DELETE SET NULL,
  CONSTRAINT `generated_reports_report_schedule_id_foreign` FOREIGN KEY (`report_schedule_id`) REFERENCES `report_schedules` (`id`) ON DELETE SET NULL,
  CONSTRAINT `generated_reports_generated_by_foreign` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `email_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NULL,
  `generated_report_id` BIGINT UNSIGNED NULL,
  `subject` VARCHAR(255) NOT NULL,
  `recipients` JSON NOT NULL,
  `cc` JSON NULL,
  `bcc` JSON NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT 'queued',
  `provider_message_id` VARCHAR(255) NULL,
  `attachment_paths` JSON NULL,
  `meta` JSON NULL,
  `error_message` TEXT NULL,
  `sent_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `email_logs_status_sent_at_index` (`status`, `sent_at`),
  KEY `email_logs_client_id_foreign` (`client_id`),
  KEY `email_logs_generated_report_id_foreign` (`generated_report_id`),
  CONSTRAINT `email_logs_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `email_logs_generated_report_id_foreign` FOREIGN KEY (`generated_report_id`) REFERENCES `generated_reports` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `alert_rules` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NULL,
  `platform` VARCHAR(40) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `metric` VARCHAR(255) NOT NULL,
  `operator` VARCHAR(20) NOT NULL,
  `threshold` DECIMAL(18,4) NOT NULL,
  `period` VARCHAR(30) NOT NULL DEFAULT 'daily',
  `severity` VARCHAR(20) NOT NULL DEFAULT 'warning',
  `notification_channels` JSON NULL,
  `notification_emails` JSON NULL,
  `cooldown_minutes` INT UNSIGNED NOT NULL DEFAULT 1440,
  `last_evaluated_at` TIMESTAMP NULL DEFAULT NULL,
  `last_triggered_at` TIMESTAMP NULL DEFAULT NULL,
  `last_value` DECIMAL(18,6) NULL,
  `last_status` VARCHAR(30) NULL,
  `meta` JSON NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `alert_rules_client_id_platform_is_active_index` (`client_id`, `platform`, `is_active`),
  CONSTRAINT `alert_rules_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `alert_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `alert_rule_id` BIGINT UNSIGNED NULL,
  `client_id` BIGINT UNSIGNED NULL,
  `platform` VARCHAR(40) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NULL,
  `severity` VARCHAR(20) NOT NULL DEFAULT 'warning',
  `metric` VARCHAR(120) NULL,
  `operator` VARCHAR(20) NULL,
  `threshold` DECIMAL(18,6) NULL,
  `current_value` DECIMAL(18,6) NULL,
  `period` VARCHAR(30) NULL,
  `period_start` DATE NULL,
  `period_end` DATE NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'open',
  `fingerprint` VARCHAR(64) NULL,
  `payload` JSON NULL,
  `triggered_at` TIMESTAMP NULL DEFAULT NULL,
  `notified_at` TIMESTAMP NULL DEFAULT NULL,
  `resolved_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `alert_logs_client_id_platform_severity_index` (`client_id`, `platform`, `severity`),
  KEY `alert_logs_alert_rule_id_foreign` (`alert_rule_id`),
  KEY `alert_logs_fingerprint_index` (`fingerprint`),
  KEY `alert_logs_status_triggered_idx` (`status`, `triggered_at`),
  KEY `alert_logs_client_status_idx` (`client_id`, `status`, `triggered_at`),
  CONSTRAINT `alert_logs_alert_rule_id_foreign` FOREIGN KEY (`alert_rule_id`) REFERENCES `alert_rules` (`id`) ON DELETE SET NULL,
  CONSTRAINT `alert_logs_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `api_request_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NULL,
  `platform` VARCHAR(40) NOT NULL,
  `endpoint` VARCHAR(255) NULL,
  `method` VARCHAR(10) NOT NULL DEFAULT 'GET',
  `status_code` SMALLINT UNSIGNED NULL,
  `duration_ms` INT UNSIGNED NULL,
  `request_hash` VARCHAR(255) NULL,
  `error_message` TEXT NULL,
  `requested_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `api_request_logs_platform_status_code_requested_at_index` (`platform`, `status_code`, `requested_at`),
  KEY `api_request_logs_client_id_foreign` (`client_id`),
  CONSTRAINT `api_request_logs_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `settings` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group` VARCHAR(80) NOT NULL DEFAULT 'general',
  `key` VARCHAR(255) NOT NULL,
  `value` LONGTEXT NULL,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_group_key_unique` (`group`, `key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `google_campaign_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `platform_connection_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `campaign_id` VARCHAR(80) NOT NULL,
  `campaign_name` VARCHAR(255) NULL,
  `campaign_status` VARCHAR(80) NULL,
  `impressions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `clicks` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `cost` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `conversions` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `ctr` DECIMAL(12,6) NOT NULL DEFAULT 0,
  `average_cpc` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `raw_json` LONGTEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `g_campaign_unique` (`platform_connection_id`, `date`, `campaign_id`),
  KEY `google_campaign_snapshots_client_id_date_index` (`client_id`, `date`),
  CONSTRAINT `google_campaign_snapshots_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `google_campaign_snapshots_platform_connection_id_foreign` FOREIGN KEY (`platform_connection_id`) REFERENCES `platform_connections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `google_keyword_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `platform_connection_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `campaign_id` VARCHAR(80) NULL,
  `campaign_name` VARCHAR(255) NULL,
  `ad_group_id` VARCHAR(80) NULL,
  `ad_group_name` VARCHAR(255) NULL,
  `criterion_id` VARCHAR(80) NULL,
  `keyword_text` VARCHAR(1024) NULL,
  `match_type` VARCHAR(80) NULL,
  `impressions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `clicks` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `cost` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `conversions` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `ctr` DECIMAL(12,6) NOT NULL DEFAULT 0,
  `average_cpc` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `raw_json` LONGTEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  `row_hash` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `g_keyword_unique` (`platform_connection_id`, `date`, `row_hash`),
  KEY `google_keyword_snapshots_client_id_date_index` (`client_id`, `date`),
  CONSTRAINT `google_keyword_snapshots_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `google_keyword_snapshots_platform_connection_id_foreign` FOREIGN KEY (`platform_connection_id`) REFERENCES `platform_connections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `google_search_term_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `platform_connection_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `campaign_id` VARCHAR(80) NULL,
  `campaign_name` VARCHAR(255) NULL,
  `ad_group_id` VARCHAR(80) NULL,
  `ad_group_name` VARCHAR(255) NULL,
  `search_term` VARCHAR(1024) NULL,
  `match_type` VARCHAR(80) NULL,
  `impressions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `clicks` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `cost` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `conversions` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `ctr` DECIMAL(12,6) NOT NULL DEFAULT 0,
  `average_cpc` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `raw_json` LONGTEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  `row_hash` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `g_search_term_unique` (`platform_connection_id`, `date`, `row_hash`),
  KEY `google_search_term_snapshots_client_id_date_index` (`client_id`, `date`),
  CONSTRAINT `google_search_term_snapshots_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `google_search_term_snapshots_platform_connection_id_foreign` FOREIGN KEY (`platform_connection_id`) REFERENCES `platform_connections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `meta_campaign_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `platform_connection_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `campaign_id` VARCHAR(80) NOT NULL,
  `campaign_name` VARCHAR(255) NULL,
  `objective` VARCHAR(120) NULL,
  `spend` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `impressions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `reach` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `clicks` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `inline_link_clicks` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `cpc` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `cpm` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `ctr` DECIMAL(12,6) NOT NULL DEFAULT 0,
  `conversions` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `leads` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `purchases` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `purchase_value` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `raw_json` LONGTEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `m_campaign_unique` (`platform_connection_id`, `date`, `campaign_id`),
  KEY `meta_campaign_snapshots_client_id_date_index` (`client_id`, `date`),
  CONSTRAINT `meta_campaign_snapshots_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `meta_campaign_snapshots_platform_connection_id_foreign` FOREIGN KEY (`platform_connection_id`) REFERENCES `platform_connections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `meta_adset_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `platform_connection_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `campaign_id` VARCHAR(80) NULL,
  `campaign_name` VARCHAR(255) NULL,
  `adset_id` VARCHAR(80) NOT NULL,
  `adset_name` VARCHAR(255) NULL,
  `spend` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `impressions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `reach` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `clicks` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `inline_link_clicks` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `cpc` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `cpm` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `ctr` DECIMAL(12,6) NOT NULL DEFAULT 0,
  `conversions` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `leads` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `purchases` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `purchase_value` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `raw_json` LONGTEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `m_adset_unique` (`platform_connection_id`, `date`, `adset_id`),
  KEY `meta_adset_snapshots_client_id_date_index` (`client_id`, `date`),
  CONSTRAINT `meta_adset_snapshots_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `meta_adset_snapshots_platform_connection_id_foreign` FOREIGN KEY (`platform_connection_id`) REFERENCES `platform_connections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `meta_ad_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `platform_connection_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `campaign_id` VARCHAR(80) NULL,
  `campaign_name` VARCHAR(255) NULL,
  `adset_id` VARCHAR(80) NULL,
  `adset_name` VARCHAR(255) NULL,
  `ad_id` VARCHAR(80) NOT NULL,
  `ad_name` VARCHAR(255) NULL,
  `spend` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `impressions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `reach` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `clicks` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `inline_link_clicks` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `cpc` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `cpm` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `ctr` DECIMAL(12,6) NOT NULL DEFAULT 0,
  `conversions` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `leads` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `purchases` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `purchase_value` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `raw_json` LONGTEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `m_ad_unique` (`platform_connection_id`, `date`, `ad_id`),
  KEY `meta_ad_snapshots_client_id_date_index` (`client_id`, `date`),
  CONSTRAINT `meta_ad_snapshots_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `meta_ad_snapshots_platform_connection_id_foreign` FOREIGN KEY (`platform_connection_id`) REFERENCES `platform_connections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `search_console_query_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `platform_connection_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `query_text` VARCHAR(1024) NULL,
  `clicks` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `impressions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `ctr` DECIMAL(12,6) NOT NULL DEFAULT 0,
  `position` DECIMAL(12,4) NOT NULL DEFAULT 0,
  `raw_json` LONGTEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  `row_hash` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gsc_query_unique` (`platform_connection_id`, `date`, `row_hash`),
  KEY `search_console_query_snapshots_client_id_date_index` (`client_id`, `date`),
  CONSTRAINT `search_console_query_snapshots_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `search_console_query_snapshots_platform_connection_id_foreign` FOREIGN KEY (`platform_connection_id`) REFERENCES `platform_connections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `search_console_page_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `platform_connection_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `page_url` TEXT NULL,
  `clicks` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `impressions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `ctr` DECIMAL(12,6) NOT NULL DEFAULT 0,
  `position` DECIMAL(12,4) NOT NULL DEFAULT 0,
  `raw_json` LONGTEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  `row_hash` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gsc_page_unique` (`platform_connection_id`, `date`, `row_hash`),
  KEY `search_console_page_snapshots_client_id_date_index` (`client_id`, `date`),
  CONSTRAINT `search_console_page_snapshots_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `search_console_page_snapshots_platform_connection_id_foreign` FOREIGN KEY (`platform_connection_id`) REFERENCES `platform_connections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ga4_traffic_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `platform_connection_id` BIGINT UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `dimension_type` VARCHAR(120) NOT NULL DEFAULT 'channel_group',
  `dimension_value` VARCHAR(255) NULL,
  `sessions` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `total_users` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `screen_page_views` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `conversions` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `total_revenue` DECIMAL(18,6) NOT NULL DEFAULT 0,
  `event_count` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `raw_json` LONGTEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ga4_traffic_unique` (`platform_connection_id`, `date`, `dimension_type`, `dimension_value`),
  KEY `ga4_traffic_snapshots_client_id_date_index` (`client_id`, `date`),
  CONSTRAINT `ga4_traffic_snapshots_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ga4_traffic_snapshots_platform_connection_id_foreign` FOREIGN KEY (`platform_connection_id`) REFERENCES `platform_connections` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `operation_tasks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `category` VARCHAR(60) NOT NULL DEFAULT 'general',
  `priority` VARCHAR(20) NOT NULL DEFAULT 'medium',
  `status` VARCHAR(30) NOT NULL DEFAULT 'open',
  `source_type` VARCHAR(80) NULL,
  `source_id` BIGINT UNSIGNED NULL,
  `assigned_to` BIGINT UNSIGNED NULL,
  `due_at` TIMESTAMP NULL DEFAULT NULL,
  `completed_at` TIMESTAMP NULL DEFAULT NULL,
  `meta` JSON NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `operation_tasks_client_id_status_priority_index` (`client_id`, `status`, `priority`),
  KEY `operation_tasks_category_status_index` (`category`, `status`),
  KEY `operation_tasks_source_type_source_id_index` (`source_type`, `source_id`),
  KEY `operation_tasks_assigned_to_foreign` (`assigned_to`),
  KEY `operation_tasks_created_by_foreign` (`created_by`),
  CONSTRAINT `operation_tasks_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `operation_tasks_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `operation_tasks_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `client_health_scores` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NOT NULL,
  `score_date` DATE NOT NULL,
  `score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `status` VARCHAR(30) NOT NULL DEFAULT 'watch',
  `readiness` VARCHAR(30) NULL,
  `api_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `data_quality_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `report_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `mail_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `alert_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `task_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `onboarding_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `breakdown` JSON NULL,
  `risk_items` JSON NULL,
  `recommendations` JSON NULL,
  `calculated_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `client_health_scores_client_id_score_date_unique` (`client_id`, `score_date`),
  KEY `client_health_scores_score_date_index` (`score_date`),
  KEY `client_health_scores_status_index` (`status`),
  KEY `client_health_scores_readiness_index` (`readiness`),
  CONSTRAINT `client_health_scores_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `agency_management_reports` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `period_type` VARCHAR(255) NOT NULL DEFAULT 'weekly',
  `date_from` DATE NOT NULL,
  `date_to` DATE NOT NULL,
  `status` VARCHAR(255) NOT NULL DEFAULT 'completed',
  `overall_score` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `summary` JSON NULL,
  `kpis` JSON NULL,
  `risk_clients` JSON NULL,
  `top_clients` JSON NULL,
  `team_workload` JSON NULL,
  `channel_health` JSON NULL,
  `recommendations` JSON NULL,
  `html_path` VARCHAR(255) NULL,
  `csv_path` VARCHAR(255) NULL,
  `generated_at` TIMESTAMP NULL DEFAULT NULL,
  `generated_by` BIGINT UNSIGNED NULL,
  `error_message` TEXT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `agency_management_reports_period_type_index` (`period_type`),
  KEY `agency_management_reports_date_from_index` (`date_from`),
  KEY `agency_management_reports_date_to_index` (`date_to`),
  KEY `agency_management_reports_status_index` (`status`),
  KEY `agency_management_reports_period_type_date_from_date_to_index` (`period_type`, `date_from`, `date_to`),
  KEY `agency_management_reports_generated_by_foreign` (`generated_by`),
  CONSTRAINT `agency_management_reports_generated_by_foreign` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `dw_notifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `client_id` BIGINT UNSIGNED NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `fingerprint` VARCHAR(191) NOT NULL,
  `category` VARCHAR(60) NOT NULL,
  `source_type` VARCHAR(80) NULL,
  `source_id` BIGINT UNSIGNED NULL,
  `level` VARCHAR(30) NOT NULL DEFAULT 'info',
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NULL,
  `action_label` VARCHAR(255) NULL,
  `action_url` TEXT NULL,
  `payload` JSON NULL,
  `read_at` TIMESTAMP NULL DEFAULT NULL,
  `dismissed_at` TIMESTAMP NULL DEFAULT NULL,
  `expires_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dw_notifications_fingerprint_unique` (`fingerprint`),
  KEY `dw_notifications_category_index` (`category`),
  KEY `dw_notifications_source_type_index` (`source_type`),
  KEY `dw_notifications_source_id_index` (`source_id`),
  KEY `dw_notifications_level_index` (`level`),
  KEY `dw_notifications_read_at_index` (`read_at`),
  KEY `dw_notifications_dismissed_at_index` (`dismissed_at`),
  KEY `dw_notifications_expires_at_index` (`expires_at`),
  KEY `dw_notifications_client_id_level_read_at_index` (`client_id`, `level`, `read_at`),
  KEY `dw_notifications_category_created_at_index` (`category`, `created_at`),
  KEY `dw_notifications_user_id_foreign` (`user_id`),
  CONSTRAINT `dw_notifications_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL,
  CONSTRAINT `dw_notifications_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `migrations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `migration` VARCHAR(255) NOT NULL,
  `batch` INT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;

INSERT INTO `brand_kits` (`client_id`, `name`, `description`, `agency_name`, `agency_logo_url`, `primary_color`, `accent_color`, `secondary_color`, `text_color`, `muted_color`, `cover_style`, `footer_note`, `website_url`, `settings`, `is_default`, `is_active`, `sort_order`, `created_at`, `updated_at`)
SELECT NULL, 'DW Varsayılan Marka Kiti', 'Tüm müşteriler için kullanılabilecek varsayılan Datweb rapor kimliği.', 'Datweb', 'https://datweb.com.tr/wp-content/uploads/2024/05/datweb-logo.png', '#111827', '#ef5f86', '#f8fafc', '#111827', '#6b7280', 'gradient', 'Bu rapor Datweb reklam raporlama paneli tarafından otomatik oluşturulmuştur.', 'https://datweb.com.tr', '{"created_by_migration":true}', 1, 1, 10, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `brand_kits` WHERE `client_id` IS NULL AND `is_default` = 1);

INSERT INTO `report_templates` (`client_id`, `brand_kit_id`, `name`, `description`, `type`, `channels`, `sections`, `settings`, `is_default`, `is_active`, `sort_order`, `created_at`, `updated_at`)
SELECT NULL, NULL, 'Günlük Hızlı Özet', 'Her sabah gönderilecek kısa reklam/SEO özeti.', 'daily', '["google_ads","meta_ads","search_console","ga4"]', '{"cover":true,"overview_cards":true,"executive_summary":true,"ai_summary":true,"channel_comparison":true,"google_campaigns":true,"google_keywords":false,"google_search_terms":false,"meta_campaigns":true,"meta_adsets":false,"meta_ads":false,"gsc_queries":true,"gsc_pages":false,"ga4_channels":true,"footer":true}', '{"agency_name":"DW Ads Reporter","agency_logo_url":"","client_logo_url":"","primary_color":"#111827","accent_color":"#ef5f86","secondary_color":"#f8fafc","text_color":"#111827","muted_color":"#6b7280","cover_style":"gradient","cover_background_url":"","cover_title":"Günlük Dijital Performans Özeti","cover_subtitle":"Dünkü harcama, tıklama, dönüşüm ve organik görünürlük özeti","intro_text":"Bu rapor seçilen tarih aralığında toplanan reklam, SEO ve analitik verileri üzerinden otomatik hazırlanmıştır.","footer_note":"Bu rapor DW Ads Reporter tarafından otomatik oluşturulmuştur.","contact_email":"","contact_phone":"","website_url":"","table_limit":10,"show_client_logo":true,"show_agency_logo":true,"show_data_timestamp":true,"compact_mode":true,"include_pdf_cover":true,"include_excel_summary":true}', 1, 1, 10, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `report_templates` WHERE `name` = 'Günlük Hızlı Özet' AND `client_id` IS NULL);
INSERT INTO `report_templates` (`client_id`, `brand_kit_id`, `name`, `description`, `type`, `channels`, `sections`, `settings`, `is_default`, `is_active`, `sort_order`, `created_at`, `updated_at`)
SELECT NULL, NULL, 'Haftalık Performans Raporu', 'Son 7 gün ve önceki dönem karşılaştırması için standart şablon.', 'weekly', '["google_ads","meta_ads","search_console","ga4"]', '{"cover":true,"overview_cards":true,"executive_summary":true,"ai_summary":true,"channel_comparison":true,"google_campaigns":true,"google_keywords":true,"google_search_terms":false,"meta_campaigns":true,"meta_adsets":true,"meta_ads":false,"gsc_queries":true,"gsc_pages":true,"ga4_channels":true,"footer":true}', '{"agency_name":"DW Ads Reporter","agency_logo_url":"","client_logo_url":"","primary_color":"#111827","accent_color":"#ef5f86","secondary_color":"#f8fafc","text_color":"#111827","muted_color":"#6b7280","cover_style":"gradient","cover_background_url":"","cover_title":"Haftalık Dijital Performans Raporu","cover_subtitle":"Haftalık kanal performansı, riskler, fırsatlar ve aksiyon önerileri","intro_text":"Bu rapor seçilen tarih aralığında toplanan reklam, SEO ve analitik verileri üzerinden otomatik hazırlanmıştır.","footer_note":"Bu rapor DW Ads Reporter tarafından otomatik oluşturulmuştur.","contact_email":"","contact_phone":"","website_url":"","table_limit":15,"show_client_logo":true,"show_agency_logo":true,"show_data_timestamp":true,"compact_mode":false,"include_pdf_cover":true,"include_excel_summary":true}', 1, 1, 20, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `report_templates` WHERE `name` = 'Haftalık Performans Raporu' AND `client_id` IS NULL);
INSERT INTO `report_templates` (`client_id`, `brand_kit_id`, `name`, `description`, `type`, `channels`, `sections`, `settings`, `is_default`, `is_active`, `sort_order`, `created_at`, `updated_at`)
SELECT NULL, NULL, 'Aylık Yönetici Raporu', 'Müşteri ve yönetici sunumu için detaylı aylık rapor.', 'monthly', '["google_ads","meta_ads","search_console","ga4"]', '{"cover":true,"overview_cards":true,"executive_summary":true,"ai_summary":true,"channel_comparison":true,"google_campaigns":true,"google_keywords":true,"google_search_terms":true,"meta_campaigns":true,"meta_adsets":true,"meta_ads":true,"gsc_queries":true,"gsc_pages":true,"ga4_channels":true,"footer":true}', '{"agency_name":"DW Ads Reporter","agency_logo_url":"","client_logo_url":"","primary_color":"#111827","accent_color":"#ef5f86","secondary_color":"#f8fafc","text_color":"#111827","muted_color":"#6b7280","cover_style":"gradient","cover_background_url":"","cover_title":"Aylık Dijital Pazarlama Raporu","cover_subtitle":"Reklam, SEO, Analytics ve öneri odaklı yönetici raporu","intro_text":"Bu rapor seçilen tarih aralığında toplanan reklam, SEO ve analitik verileri üzerinden otomatik hazırlanmıştır.","footer_note":"Bu rapor DW Ads Reporter tarafından otomatik oluşturulmuştur.","contact_email":"","contact_phone":"","website_url":"","table_limit":25,"show_client_logo":true,"show_agency_logo":true,"show_data_timestamp":true,"compact_mode":false,"include_pdf_cover":true,"include_excel_summary":true}', 1, 1, 30, NOW(), NOW()
WHERE NOT EXISTS (SELECT 1 FROM `report_templates` WHERE `name` = 'Aylık Yönetici Raporu' AND `client_id` IS NULL);

INSERT INTO `migrations` (`migration`, `batch`) SELECT '2014_10_12_000000_create_users_table', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2014_10_12_000000_create_users_table');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2014_10_12_100000_create_password_reset_tokens_table', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2014_10_12_100000_create_password_reset_tokens_table');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2019_08_19_000000_create_failed_jobs_table', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2019_08_19_000000_create_failed_jobs_table');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2019_12_14_000001_create_personal_access_tokens_table', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2019_12_14_000001_create_personal_access_tokens_table');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000001_create_dw_ads_core_tables', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000001_create_dw_ads_core_tables');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000002_create_dw_ads_snapshot_tables', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000002_create_dw_ads_snapshot_tables');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000003_add_report_artifact_columns', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000003_add_report_artifact_columns');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000004_add_mail_automation_columns', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000004_add_mail_automation_columns');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000005_enhance_alert_engine_tables', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000005_enhance_alert_engine_tables');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000006_add_customer_portal_fields', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000006_add_customer_portal_fields');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000007_add_ai_insights_to_generated_reports', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000007_add_ai_insights_to_generated_reports');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000014_enhance_report_templates', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000014_enhance_report_templates');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000015_create_brand_kits', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000015_create_brand_kits');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000016_add_onboarding_fields_to_clients', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000016_add_onboarding_fields_to_clients');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000018_create_operation_tasks', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000018_create_operation_tasks');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000020_create_client_health_scores_table', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000020_create_client_health_scores_table');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_19_000021_create_agency_management_reports_table', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_19_000021_create_agency_management_reports_table');
INSERT INTO `migrations` (`migration`, `batch`) SELECT '2026_05_21_000023_create_dw_notifications_table', 1 WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE `migration` = '2026_05_21_000023_create_dw_notifications_table');
