-- Eliminar tablas si existen para evitar errores

-- Crear tabla de campañas
CREATE TABLE campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    destination_url VARCHAR(255) NOT NULL,
    popup_content TEXT NOT NULL,
    display_time INT NOT NULL DEFAULT 5,
    campaign_order INT NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Crear tabla de relación entre países y campañas
CREATE TABLE country_campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    country_code CHAR(2) DEFAULT NULL, -- NULL significa todos los países
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
    UNIQUE KEY unique_campaign_country (campaign_id, country_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Crear tabla de estadísticas
CREATE TABLE stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    referrer VARCHAR(255),
    country_code CHAR(2),
    clicked TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Crear tabla de configuraciones
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) NOT NULL UNIQUE,
    setting_value TEXT,
    setting_description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar configuraciones predeterminadas
INSERT INTO settings (setting_key, setting_value, setting_description) VALUES
('popup_title', 'Special Offer', 'Default title for popup windows'),
('popup_delay', '5', 'Time in seconds before showing popup'),
('popup_frequency', '24', 'Hours to wait before showing popup again to same user'),
('allow_close', '1', 'Whether to show close button on popup'),
('api_enabled', '1', 'Enable API access for external applications'),
('api_key', MD5(RAND()), 'API key for authentication'),
('popup_theme', 'light', 'Visual theme for popup (light, dark, blue, green, red)'),
('popup_position', 'center', 'Position of popup on screen (center, top, bottom, left, right)'),
('popup_animation', 'fade', 'Animation effect for popup (fade, slide, zoom, none)'),
('custom_css', '', 'Custom CSS for popup styling');