CREATE TABLE facebook_ad_accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    integration_id INT UNSIGNED NOT NULL,
    ad_account_id VARCHAR(50) NOT NULL,
    name VARCHAR(160) NOT NULL,
    account_status VARCHAR(30) NULL,
    currency VARCHAR(10) NULL,
    timezone_name VARCHAR(80) NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    last_synced_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_facebook_ad_account_integration
        FOREIGN KEY (integration_id) REFERENCES facebook_integrations (id)
        ON DELETE CASCADE,
    UNIQUE KEY idx_facebook_ad_account_unique (integration_id, ad_account_id),
    INDEX idx_facebook_ad_account_default (integration_id, is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE facebook_lead_syncs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    integration_id INT UNSIGNED NOT NULL,
    page_id VARCHAR(50) NULL,
    form_id VARCHAR(50) NOT NULL,
    facebook_lead_id VARCHAR(80) NOT NULL,
    lead_id INT UNSIGNED NULL,
    payload JSON NULL,
    received_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_facebook_lead_sync_integration
        FOREIGN KEY (integration_id) REFERENCES facebook_integrations (id)
        ON DELETE CASCADE,
    CONSTRAINT fk_facebook_lead_sync_lead
        FOREIGN KEY (lead_id) REFERENCES leads (id)
        ON DELETE SET NULL,
    UNIQUE KEY idx_facebook_lead_unique (integration_id, facebook_lead_id),
    INDEX idx_facebook_lead_form (form_id),
    INDEX idx_facebook_lead_lead (lead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE facebook_ads_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    integration_id INT UNSIGNED NOT NULL,
    ad_account_id VARCHAR(50) NOT NULL,
    name VARCHAR(160) NOT NULL,
    scope ENUM('campaign', 'adset', 'ad') NOT NULL DEFAULT 'campaign',
    target_id VARCHAR(80) NULL,
    metric VARCHAR(50) NOT NULL,
    operator ENUM('gt', 'gte', 'lt', 'lte', 'eq') NOT NULL DEFAULT 'gt',
    threshold DECIMAL(14,4) NOT NULL DEFAULT 0,
    action ENUM('pause', 'activate', 'increase_budget', 'decrease_budget') NOT NULL,
    budget_delta DECIMAL(14,2) NULL,
    budget_delta_type ENUM('absolute', 'percent') NOT NULL DEFAULT 'absolute',
    date_preset VARCHAR(40) NOT NULL DEFAULT 'last_7d',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_run_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_facebook_ads_rule_integration
        FOREIGN KEY (integration_id) REFERENCES facebook_integrations (id)
        ON DELETE CASCADE,
    CONSTRAINT fk_facebook_ads_rule_creator
        FOREIGN KEY (created_by) REFERENCES users (id)
        ON DELETE SET NULL,
    INDEX idx_facebook_ads_rule_account (ad_account_id),
    INDEX idx_facebook_ads_rule_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;