SET NAMES utf8mb4;

ALTER TABLE content_variants
    ADD COLUMN performance_score DECIMAL(12,4) NOT NULL DEFAULT 0 AFTER status,
    ADD COLUMN last_selected_at DATETIME NULL AFTER performance_score;

ALTER TABLE destinations
    ADD COLUMN health_score DECIMAL(8,2) NOT NULL DEFAULT 100 AFTER status,
    ADD COLUMN last_success_at DATETIME NULL AFTER health_score,
    ADD COLUMN last_failure_at DATETIME NULL AFTER last_success_at;

CREATE TABLE IF NOT EXISTS integrations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    workspace_id BIGINT UNSIGNED NOT NULL,
    channel_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(180) NOT NULL,
    adapter VARCHAR(160) NOT NULL,
    status ENUM('active','inactive','error') NOT NULL DEFAULT 'inactive',
    credentials_encrypted LONGTEXT NULL,
    settings JSON NULL,
    last_tested_at DATETIME NULL,
    last_error TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_integrations_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
    CONSTRAINT fk_integrations_channel FOREIGN KEY(channel_id) REFERENCES channels(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS publication_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    workspace_id BIGINT UNSIGNED NOT NULL,
    scheduled_post_id BIGINT UNSIGNED NOT NULL,
    integration_id BIGINT UNSIGNED NULL,
    attempt_no INT UNSIGNED NOT NULL DEFAULT 1,
    result_status VARCHAR(80) NOT NULL,
    response_code VARCHAR(80) NULL,
    response_excerpt TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pub_attempt_post(scheduled_post_id,created_at),
    CONSTRAINT fk_pa_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
    CONSTRAINT fk_pa_post FOREIGN KEY(scheduled_post_id) REFERENCES scheduled_posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_pa_integration FOREIGN KEY(integration_id) REFERENCES integrations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS campaign_automation_settings (
    campaign_id BIGINT UNSIGNED PRIMARY KEY,
    workspace_id BIGINT UNSIGNED NOT NULL,
    auto_compose TINYINT(1) NOT NULL DEFAULT 0,
    auto_schedule TINYINT(1) NOT NULL DEFAULT 0,
    prefer_winners TINYINT(1) NOT NULL DEFAULT 1,
    exploration_percent TINYINT UNSIGNED NOT NULL DEFAULT 20,
    require_manual_approval TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_cas_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    CONSTRAINT fk_cas_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
