SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    workspace_id BIGINT UNSIGNED NOT NULL,
    type ENUM('info','success','warning','error') NOT NULL DEFAULT 'info',
    title VARCHAR(190) NOT NULL,
    message TEXT NOT NULL,
    action_url TEXT NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    read_at DATETIME NULL,
    INDEX idx_notifications_ws_read(workspace_id,is_read,created_at),
    CONSTRAINT fk_notifications_workspace FOREIGN KEY(workspace_id)
        REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS content_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    workspace_id BIGINT UNSIGNED NOT NULL,
    campaign_id BIGINT UNSIGNED NULL,
    name VARCHAR(180) NOT NULL,
    headline_template TEXT NULL,
    body_template LONGTEXT NULL,
    cta_template VARCHAR(255) NULL,
    status ENUM('active','paused','archived') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_ct_workspace FOREIGN KEY(workspace_id)
        REFERENCES workspaces(id) ON DELETE CASCADE,
    CONSTRAINT fk_ct_campaign FOREIGN KEY(campaign_id)
        REFERENCES campaigns(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS import_batches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    workspace_id BIGINT UNSIGNED NOT NULL,
    import_type VARCHAR(80) NOT NULL,
    original_filename VARCHAR(255) NULL,
    inserted_count INT UNSIGNED NOT NULL DEFAULT 0,
    skipped_count INT UNSIGNED NOT NULL DEFAULT 0,
    status ENUM('processing','completed','failed') NOT NULL DEFAULT 'processing',
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    finished_at DATETIME NULL,
    CONSTRAINT fk_ib_workspace FOREIGN KEY(workspace_id)
        REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE integrations
    ADD COLUMN auth_type ENUM('oauth','token','api_key','basic','none')
        NOT NULL DEFAULT 'none' AFTER adapter,
    ADD COLUMN last_success_at DATETIME NULL AFTER last_tested_at;

ALTER TABLE campaign_automation_settings
    ADD COLUMN winner_min_uses INT UNSIGNED NOT NULL DEFAULT 5 AFTER exploration_percent,
    ADD COLUMN winner_min_conversions INT UNSIGNED NOT NULL DEFAULT 1 AFTER winner_min_uses;
