-- Create database
CREATE DATABASE IF NOT EXISTS tracking_system;
USE tracking_system;

-- Create users table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create pixels table
CREATE TABLE pixels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pixel_id VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_pixel_per_user (pixel_id, user_id)
);

-- Create domains table
CREATE TABLE domains (
    id INT AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(255) NOT NULL,
    aw_id VARCHAR(50) NOT NULL,
    conversion_id VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create pixel_domains table (many-to-many relationship)
CREATE TABLE pixel_domains (
    pixel_id INT,
    domain_id INT,
    PRIMARY KEY (pixel_id, domain_id),
    FOREIGN KEY (pixel_id) REFERENCES pixels(id) ON DELETE CASCADE,
    FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
);

-- Create conversions table for tracking
CREATE TABLE conversions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pixel_id INT,
    gclid VARCHAR(255),
    value DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pixel_id) REFERENCES pixels(id),
    UNIQUE KEY unique_gclid_value (gclid, value)
);

-- Add indexes for better performance
CREATE INDEX idx_pixel_id ON pixels(pixel_id);
CREATE INDEX idx_gclid ON conversions(gclid);
CREATE INDEX idx_created_at ON conversions(created_at);
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_username ON users(username); 