CREATE TABLE IF NOT EXISTS campaigns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    meta_campaign_id VARCHAR(100) NULL UNIQUE,
    name VARCHAR(190) NOT NULL,
    objective VARCHAR(80) NOT NULL DEFAULT 'LEADS',
    status VARCHAR(30) NOT NULL DEFAULT 'DEMO',
    spend DECIMAL(12,2) NOT NULL DEFAULT 0,
    impressions BIGINT UNSIGNED NOT NULL DEFAULT 0,
    clicks BIGINT UNSIGNED NOT NULL DEFAULT 0,
    leads BIGINT UNSIGNED NOT NULL DEFAULT 0,
    purchases BIGINT UNSIGNED NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    data_source ENUM('demo','manual','meta_api') NOT NULL DEFAULT 'demo',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS action_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id BIGINT UNSIGNED NULL,
    action_type VARCHAR(80) NOT NULL,
    mode ENUM('manual','automatic') NOT NULL DEFAULT 'manual',
    description TEXT NOT NULL,
    status ENUM('suggested','approved','executed','rejected','failed') NOT NULL DEFAULT 'suggested',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_action_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS app_settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT NOT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO campaigns
(meta_campaign_id, name, objective, status, spend, impressions, clicks, leads, purchases, currency, data_source)
VALUES
(NULL, 'Exemple — TeachingMe inscriptions', 'LEADS', 'DEMO', 10.00, 4200, 87, 14, 3, 'USD', 'demo'),
(NULL, 'Exemple — MyClasseRoom', 'LEADS', 'DEMO', 7.50, 3100, 64, 9, 1, 'USD', 'demo');

INSERT INTO app_settings (setting_key, setting_value) VALUES
('mode', 'manual'),
('automatic_mode_enabled', '0'),
('daily_spend_limit', '10.00')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);