-- Crea il database
CREATE DATABASE IF NOT EXISTS splittracking;
USE splittracking;

-- Tabella delle visite
CREATE TABLE IF NOT EXISTS visits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version VARCHAR(10) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    utm_source VARCHAR(100),
    utm_medium VARCHAR(100),
    utm_campaign VARCHAR(100),
    fbclid VARCHAR(100),
    ad_id VARCHAR(100),
    adset_id VARCHAR(100),
    campaign_id VARCHAR(100),
    visit_time DATETIME NOT NULL,
    INDEX idx_version (version),
    INDEX idx_visit_time (visit_time)
);

-- Tabella dei click
CREATE TABLE IF NOT EXISTS clicks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT,
    version VARCHAR(10) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    referrer TEXT,
    fbclid VARCHAR(100),
    click_time DATETIME NOT NULL,
    INDEX idx_version (version),
    INDEX idx_click_time (click_time),
    INDEX idx_visit_id (visit_id),
    FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE
);

-- Tabella delle conversioni
CREATE TABLE IF NOT EXISTS conversions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT,
    version VARCHAR(10) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    referrer TEXT,
    fbclid VARCHAR(100),
    conversion_time DATETIME NOT NULL,
    INDEX idx_version (version),
    INDEX idx_conversion_time (conversion_time),
    INDEX idx_visit_id (visit_id),
    FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE
);


GRANT SELECT, INSERT, UPDATE ON splittracking.* TO 'splittrackuser'@'localhost';
FLUSH PRIVILEGES; 