/* Execute este arquivo dentro do banco prof2543_meta_ads_manager */

CREATE TABLE IF NOT EXISTS meta_integrations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    app_id VARCHAR(100) DEFAULT NULL,
    app_secret VARCHAR(255) DEFAULT NULL,
    access_token TEXT DEFAULT NULL,
    ad_account_id VARCHAR(50) NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    sync_interval_minutes INT UNSIGNED NOT NULL DEFAULT 30,
    timezone VARCHAR(100) DEFAULT 'America/Sao_Paulo',
    last_sync_at DATETIME NULL,
    last_success_sync_at DATETIME NULL,
    last_error_at DATETIME NULL,
    last_error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_status (status),
    KEY idx_ad_account_id (ad_account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hotmart_sales_live (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    webhook_event VARCHAR(100) DEFAULT NULL,
    webhook_event_id VARCHAR(100) DEFAULT NULL,
    transaction_code VARCHAR(80) NOT NULL,
    status VARCHAR(50) DEFAULT NULL,
    transaction_date DATETIME DEFAULT NULL,
    payment_confirmed_at DATETIME DEFAULT NULL,
    refund_or_chargeback_at DATETIME DEFAULT NULL,
    product_code BIGINT UNSIGNED DEFAULT NULL,
    product_name VARCHAR(255) DEFAULT NULL,
    price_code VARCHAR(80) DEFAULT NULL,
    price_name VARCHAR(255) DEFAULT NULL,
    payment_type VARCHAR(40) DEFAULT NULL,
    installments_number INT UNSIGNED DEFAULT NULL,
    sale_origin VARCHAR(100) DEFAULT NULL,
    sales_channel VARCHAR(40) NOT NULL DEFAULT 'hotmart',
    currency VARCHAR(10) DEFAULT NULL,
    gross_revenue DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    net_revenue DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    producer_net DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    refunded_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    chargeback_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    buyer_name VARCHAR(255) DEFAULT NULL,
    buyer_email VARCHAR(255) DEFAULT NULL,
    buyer_phone_raw VARCHAR(50) DEFAULT NULL,
    buyer_phone_norm VARCHAR(20) DEFAULT NULL,
    matched_user_id BIGINT UNSIGNED DEFAULT NULL,
    match_method VARCHAR(20) NOT NULL DEFAULT 'none',
    utm_source VARCHAR(255) DEFAULT NULL,
    utm_medium VARCHAR(255) DEFAULT NULL,
    utm_campaign VARCHAR(255) DEFAULT NULL,
    utm_term VARCHAR(255) DEFAULT NULL,
    utm_content VARCHAR(255) DEFAULT NULL,
    raw_payload_json LONGTEXT DEFAULT NULL,
    imported_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hotmart_live_transaction (transaction_code),
    KEY idx_hotmart_live_status (status),
    KEY idx_hotmart_live_transaction_date (transaction_date),
    KEY idx_hotmart_live_confirmed (payment_confirmed_at),
    KEY idx_hotmart_live_product (product_code),
    KEY idx_hotmart_live_email (buyer_email),
    KEY idx_hotmart_live_phone (buyer_phone_norm),
    KEY idx_hotmart_live_user (matched_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hotmart_webhook_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_id VARCHAR(80) NOT NULL,
    event_name VARCHAR(80) DEFAULT NULL,
    transaction_code VARCHAR(80) DEFAULT NULL,
    process_status ENUM('success','error') NOT NULL DEFAULT 'success',
    process_message TEXT DEFAULT NULL,
    payload_json LONGTEXT DEFAULT NULL,
    received_at DATETIME NOT NULL,
    processed_at DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hotmart_event_id (event_id),
    KEY idx_hotmart_event_name (event_name),
    KEY idx_hotmart_event_transaction (transaction_code),
    KEY idx_hotmart_event_received (received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hotmart_sf_shadow_outbox (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    event_id VARCHAR(100) NOT NULL,
    event_name VARCHAR(100) NOT NULL,
    transaction_code VARCHAR(80) DEFAULT NULL,
    contact_email VARCHAR(255) DEFAULT NULL,
    contact_phone VARCHAR(30) DEFAULT NULL,
    payload_json LONGTEXT NOT NULL,
    status ENUM('shadow','ready','sent','failed','cancelled') NOT NULL DEFAULT 'shadow',
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    last_error TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    sent_at DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_hotmart_sf_shadow_event (event_id),
    KEY idx_hotmart_sf_shadow_status (status),
    KEY idx_hotmart_sf_shadow_event_name (event_name),
    KEY idx_hotmart_sf_shadow_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_sales (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    provider VARCHAR(30) NOT NULL,
    external_transaction_id VARCHAR(100) NOT NULL,
    external_checkout_id VARCHAR(100) NULL,
    transaction_type VARCHAR(40) NULL,
    provider_status VARCHAR(80) NULL,
    normalized_status VARCHAR(30) NOT NULL DEFAULT 'UNKNOWN',
    currency VARCHAR(10) NULL,
    gross_amount_cents BIGINT NOT NULL DEFAULT 0,
    product_amount_cents BIGINT NOT NULL DEFAULT 0,
    interest_amount_cents BIGINT NOT NULL DEFAULT 0,
    installments INT UNSIGNED NULL,
    payment_method VARCHAR(80) NULL,
    payment_gateway VARCHAR(120) NULL,
    provider_account_id VARCHAR(150) NULL,
    external_product_id VARCHAR(100) NULL,
    product_name VARCHAR(255) NULL,
    product_slug VARCHAR(255) NULL,
    integration_id VARCHAR(500) NULL,
    integration_delivery_type VARCHAR(80) NULL,
    classes_text VARCHAR(500) NULL,
    origin_description VARCHAR(255) NULL,
    origin_slug VARCHAR(255) NULL,
    buyer_name VARCHAR(255) NULL,
    buyer_email VARCHAR(255) NULL,
    buyer_phone VARCHAR(60) NULL,
    buyer_document VARCHAR(60) NULL,
    matched_user_id BIGINT UNSIGNED NULL,
    match_method VARCHAR(30) NOT NULL DEFAULT 'none',
    checkout_url VARCHAR(1000) NULL,
    order_bumps_json LONGTEXT NULL,
    raw_payload_json LONGTEXT NOT NULL,
    first_received_at DATETIME NOT NULL,
    last_received_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_payment_provider_transaction (provider, external_transaction_id),
    KEY idx_payment_status (normalized_status),
    KEY idx_payment_buyer_email (buyer_email),
    KEY idx_payment_user (matched_user_id),
    KEY idx_payment_received (last_received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS firepay_webhook_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    inbound_webhook_id INT NULL,
    event_fingerprint CHAR(64) NOT NULL,
    external_transaction_id VARCHAR(100) NULL,
    provider_status VARCHAR(80) NULL,
    process_status ENUM('success','ignored','error') NOT NULL,
    process_message TEXT NULL,
    payload_json LONGTEXT NOT NULL,
    received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_firepay_fingerprint (event_fingerprint),
    KEY idx_firepay_transaction (external_transaction_id),
    KEY idx_firepay_status (process_status),
    KEY idx_firepay_received (received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS meta_sync_runs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    integration_id BIGINT UNSIGNED NOT NULL,
    scope ENUM('account','campaign','adset','ad') NOT NULL,
    date_from DATE NOT NULL,
    date_to DATE NOT NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    status ENUM('running','success','error') NOT NULL DEFAULT 'running',
    rows_upserted INT UNSIGNED NOT NULL DEFAULT 0,
    message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_integration_id (integration_id),
    KEY idx_scope (scope),
    KEY idx_status (status),
    KEY idx_date_range (date_from, date_to),
    CONSTRAINT fk_meta_sync_runs_integration FOREIGN KEY (integration_id) REFERENCES meta_integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS meta_account_daily (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    integration_id BIGINT UNSIGNED NOT NULL,
    report_date DATE NOT NULL,
    account_id VARCHAR(50) NOT NULL,
    account_name VARCHAR(255) DEFAULT NULL,
    spend DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    impressions BIGINT UNSIGNED NOT NULL DEFAULT 0,
    reach BIGINT UNSIGNED NOT NULL DEFAULT 0,
    frequency DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    unique_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    inline_link_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    outbound_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    landing_page_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
    ctr DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    cpc DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    cpm DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    leads BIGINT UNSIGNED NOT NULL DEFAULT 0,
    purchases BIGINT UNSIGNED NOT NULL DEFAULT 0,
    purchase_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    purchase_roas DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    raw_actions_json LONGTEXT NULL,
    raw_cost_per_action_json LONGTEXT NULL,
    raw_purchase_roas_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_account_daily (integration_id, report_date, account_id),
    KEY idx_report_date (report_date),
    KEY idx_account_id (account_id),
    CONSTRAINT fk_meta_account_daily_integration FOREIGN KEY (integration_id) REFERENCES meta_integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS meta_campaign_daily (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    integration_id BIGINT UNSIGNED NOT NULL,
    report_date DATE NOT NULL,
    account_id VARCHAR(50) NOT NULL,
    campaign_id VARCHAR(50) NOT NULL,
    campaign_name VARCHAR(255) DEFAULT NULL,
    objective VARCHAR(100) DEFAULT NULL,
    buying_type VARCHAR(100) DEFAULT NULL,
    status VARCHAR(50) DEFAULT NULL,
    effective_status VARCHAR(50) DEFAULT NULL,
    spend DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    impressions BIGINT UNSIGNED NOT NULL DEFAULT 0,
    reach BIGINT UNSIGNED NOT NULL DEFAULT 0,
    frequency DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    unique_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    inline_link_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    outbound_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    landing_page_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
    ctr DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    cpc DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    cpm DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    leads BIGINT UNSIGNED NOT NULL DEFAULT 0,
    purchases BIGINT UNSIGNED NOT NULL DEFAULT 0,
    purchase_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    purchase_roas DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    raw_actions_json LONGTEXT NULL,
    raw_cost_per_action_json LONGTEXT NULL,
    raw_purchase_roas_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_campaign_daily (integration_id, report_date, campaign_id),
    KEY idx_report_date (report_date),
    KEY idx_campaign_id (campaign_id),
    KEY idx_account_id (account_id),
    KEY idx_campaign_name (campaign_name),
    CONSTRAINT fk_meta_campaign_daily_integration FOREIGN KEY (integration_id) REFERENCES meta_integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS meta_adset_daily (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    integration_id BIGINT UNSIGNED NOT NULL,
    report_date DATE NOT NULL,
    account_id VARCHAR(50) NOT NULL,
    campaign_id VARCHAR(50) NOT NULL,
    campaign_name VARCHAR(255) DEFAULT NULL,
    adset_id VARCHAR(50) NOT NULL,
    adset_name VARCHAR(255) DEFAULT NULL,
    status VARCHAR(50) DEFAULT NULL,
    effective_status VARCHAR(50) DEFAULT NULL,
    spend DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    impressions BIGINT UNSIGNED NOT NULL DEFAULT 0,
    reach BIGINT UNSIGNED NOT NULL DEFAULT 0,
    frequency DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    unique_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    inline_link_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    outbound_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    landing_page_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
    ctr DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    cpc DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    cpm DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    leads BIGINT UNSIGNED NOT NULL DEFAULT 0,
    purchases BIGINT UNSIGNED NOT NULL DEFAULT 0,
    purchase_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    purchase_roas DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    raw_actions_json LONGTEXT NULL,
    raw_cost_per_action_json LONGTEXT NULL,
    raw_purchase_roas_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_adset_daily (integration_id, report_date, adset_id),
    KEY idx_report_date (report_date),
    KEY idx_campaign_id (campaign_id),
    KEY idx_adset_id (adset_id),
    KEY idx_account_id (account_id),
    KEY idx_adset_name (adset_name),
    CONSTRAINT fk_meta_adset_daily_integration FOREIGN KEY (integration_id) REFERENCES meta_integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS meta_ad_daily (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    integration_id BIGINT UNSIGNED NOT NULL,
    report_date DATE NOT NULL,
    account_id VARCHAR(50) NOT NULL,
    campaign_id VARCHAR(50) NOT NULL,
    campaign_name VARCHAR(255) DEFAULT NULL,
    adset_id VARCHAR(50) NOT NULL,
    adset_name VARCHAR(255) DEFAULT NULL,
    ad_id VARCHAR(50) NOT NULL,
    ad_name VARCHAR(255) DEFAULT NULL,
    status VARCHAR(50) DEFAULT NULL,
    effective_status VARCHAR(50) DEFAULT NULL,
    spend DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    impressions BIGINT UNSIGNED NOT NULL DEFAULT 0,
    reach BIGINT UNSIGNED NOT NULL DEFAULT 0,
    frequency DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    unique_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    inline_link_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    outbound_clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    landing_page_views BIGINT UNSIGNED NOT NULL DEFAULT 0,
    ctr DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    cpc DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    cpm DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    leads BIGINT UNSIGNED NOT NULL DEFAULT 0,
    purchases BIGINT UNSIGNED NOT NULL DEFAULT 0,
    purchase_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    purchase_roas DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    raw_actions_json LONGTEXT NULL,
    raw_cost_per_action_json LONGTEXT NULL,
    raw_purchase_roas_json LONGTEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_ad_daily (integration_id, report_date, ad_id),
    KEY idx_report_date (report_date),
    KEY idx_campaign_id (campaign_id),
    KEY idx_adset_id (adset_id),
    KEY idx_ad_id (ad_id),
    KEY idx_account_id (account_id),
    KEY idx_ad_name (ad_name),
    CONSTRAINT fk_meta_ad_daily_integration FOREIGN KEY (integration_id) REFERENCES meta_integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attribution_runs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    run_type ENUM('import','match','daily') NOT NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    status ENUM('running','success','error') NOT NULL DEFAULT 'running',
    stats_json LONGTEXT NULL,
    message TEXT NULL,
    PRIMARY KEY (id),
    KEY idx_run_type (run_type),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attribution_leads (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_user_id BIGINT UNSIGNED NOT NULL,
    lead_name VARCHAR(255) DEFAULT NULL,
    lead_name_norm VARCHAR(255) DEFAULT NULL,
    lead_email VARCHAR(255) DEFAULT NULL,
    lead_email_norm VARCHAR(255) DEFAULT NULL,
    lead_phone_raw VARCHAR(50) DEFAULT NULL,
    lead_phone_norm VARCHAR(20) DEFAULT NULL,
    turma_codigo VARCHAR(50) DEFAULT NULL,
    created_at DATETIME NOT NULL,
    utm_source VARCHAR(255) DEFAULT NULL,
    utm_source_norm VARCHAR(255) DEFAULT NULL,
    utm_campaign_group VARCHAR(255) DEFAULT NULL,
    utm_campaign_group_norm VARCHAR(255) DEFAULT NULL,
    utm_campaign_name VARCHAR(255) DEFAULT NULL,
    utm_campaign_name_norm VARCHAR(255) DEFAULT NULL,
    utm_ad_name VARCHAR(255) DEFAULT NULL,
    utm_ad_name_norm VARCHAR(255) DEFAULT NULL,
    utm_term VARCHAR(255) DEFAULT NULL,
    utm_term_norm VARCHAR(255) DEFAULT NULL,
    imported_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_source_user_id (source_user_id),
    KEY idx_created_at (created_at),
    KEY idx_lead_phone_norm (lead_phone_norm),
    KEY idx_lead_email_norm (lead_email_norm),
    KEY idx_campaign_group_norm (utm_campaign_group_norm),
    KEY idx_campaign_name_norm (utm_campaign_name_norm),
    KEY idx_ad_name_norm (utm_ad_name_norm)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attribution_sales (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_sale_id BIGINT UNSIGNED NOT NULL,
    transaction_code VARCHAR(80) DEFAULT NULL,
    sale_status VARCHAR(50) DEFAULT NULL,
    sale_date DATETIME NOT NULL,
    payment_confirmed_at DATETIME NULL,
    product_code VARCHAR(80) DEFAULT NULL,
    product_name VARCHAR(255) DEFAULT NULL,
    price_name VARCHAR(255) DEFAULT NULL,
    currency VARCHAR(10) DEFAULT NULL,
    gross_revenue DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    net_revenue DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    producer_net DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    buyer_name VARCHAR(255) DEFAULT NULL,
    buyer_name_norm VARCHAR(255) DEFAULT NULL,
    buyer_email VARCHAR(255) DEFAULT NULL,
    buyer_email_norm VARCHAR(255) DEFAULT NULL,
    buyer_phone_raw VARCHAR(50) DEFAULT NULL,
    buyer_phone_norm VARCHAR(20) DEFAULT NULL,
    matched_user_id BIGINT UNSIGNED DEFAULT NULL,
    match_method VARCHAR(30) DEFAULT NULL,
    utm_source VARCHAR(255) DEFAULT NULL,
    utm_source_norm VARCHAR(255) DEFAULT NULL,
    utm_campaign_group VARCHAR(255) DEFAULT NULL,
    utm_campaign_group_norm VARCHAR(255) DEFAULT NULL,
    utm_campaign_name VARCHAR(255) DEFAULT NULL,
    utm_campaign_name_norm VARCHAR(255) DEFAULT NULL,
    utm_ad_name VARCHAR(255) DEFAULT NULL,
    utm_ad_name_norm VARCHAR(255) DEFAULT NULL,
    utm_term VARCHAR(255) DEFAULT NULL,
    utm_term_norm VARCHAR(255) DEFAULT NULL,
    imported_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_source_sale_id (source_sale_id),
    KEY idx_sale_date (sale_date),
    KEY idx_buyer_phone_norm (buyer_phone_norm),
    KEY idx_buyer_email_norm (buyer_email_norm),
    KEY idx_matched_user_id (matched_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attribution_matches (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sale_id BIGINT UNSIGNED NOT NULL,
    lead_id BIGINT UNSIGNED NOT NULL,
    attribution_model ENUM('first_touch','last_touch') NOT NULL,
    match_type VARCHAR(30) NOT NULL,
    attribution_seconds_diff BIGINT UNSIGNED NOT NULL DEFAULT 0,
    lead_created_at DATETIME NOT NULL,
    sale_date DATETIME NOT NULL,
    campaign_group VARCHAR(255) DEFAULT NULL,
    campaign_group_norm VARCHAR(255) DEFAULT NULL,
    campaign_name VARCHAR(255) DEFAULT NULL,
    campaign_name_norm VARCHAR(255) DEFAULT NULL,
    ad_name VARCHAR(255) DEFAULT NULL,
    ad_name_norm VARCHAR(255) DEFAULT NULL,
    revenue_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    product_name VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_sale_model (sale_id, attribution_model),
    KEY idx_lead_id (lead_id),
    KEY idx_sale_date (sale_date),
    KEY idx_campaign_group_norm (campaign_group_norm),
    KEY idx_campaign_name_norm (campaign_name_norm),
    KEY idx_ad_name_norm (ad_name_norm),
    CONSTRAINT fk_attribution_matches_sale FOREIGN KEY (sale_id) REFERENCES attribution_sales(id) ON DELETE CASCADE,
    CONSTRAINT fk_attribution_matches_lead FOREIGN KEY (lead_id) REFERENCES attribution_leads(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attribution_campaign_daily (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_date DATE NOT NULL,
    attribution_model ENUM('first_touch','last_touch') NOT NULL,
    campaign_group VARCHAR(255) DEFAULT NULL,
    campaign_group_norm VARCHAR(255) DEFAULT NULL,
    campaign_name VARCHAR(255) DEFAULT NULL,
    campaign_name_norm VARCHAR(255) DEFAULT NULL,
    ad_name VARCHAR(255) DEFAULT NULL,
    ad_name_norm VARCHAR(255) DEFAULT NULL,
    meta_spend DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    impressions BIGINT UNSIGNED NOT NULL DEFAULT 0,
    reach BIGINT UNSIGNED NOT NULL DEFAULT 0,
    clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    frequency DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    cpm DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    cpc DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    ctr DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
    leads BIGINT UNSIGNED NOT NULL DEFAULT 0,
    attributed_sales BIGINT UNSIGNED NOT NULL DEFAULT 0,
    attributed_revenue DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    cpl DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    cac DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    roas DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_attr_daily (report_date, attribution_model, campaign_group_norm, campaign_name_norm, ad_name_norm),
    KEY idx_report_model (report_date, attribution_model),
    KEY idx_campaign_group_norm (campaign_group_norm),
    KEY idx_campaign_name_norm (campaign_name_norm),
    KEY idx_ad_name_norm (ad_name_norm)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;