-- Database schema for Auto Clicker application
-- Run this first to create the necessary tables in your local MySQL database

-- Create database if it doesn't exist
CREATE DATABASE IF NOT EXISTS BigPond;
USE BigPond;

-- Create application table
CREATE TABLE IF NOT EXISTS application (
    id INT AUTO_INCREMENT PRIMARY KEY,
    platform VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    instance_id VARCHAR(255),
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create playlists table
CREATE TABLE IF NOT EXISTS playlists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `show` TINYINT(1) DEFAULT 1,
    application_id INT,
    user_id VARCHAR(255),
    company_id VARCHAR(255),
    FOREIGN KEY (application_id) REFERENCES application(id)
);

-- Create actions table
CREATE TABLE IF NOT EXISTS actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    playlist_id INT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    x INT,
    y INT,
    key_name VARCHAR(50),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    playlist_name VARCHAR(255),
    FOREIGN KEY (playlist_id) REFERENCES playlists(id)
);

-- Create direct_integration_uploads table
CREATE TABLE IF NOT EXISTS direct_integration_uploads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255),
    original_filename VARCHAR(255),
    s3_bucket VARCHAR(255),
    s3_key TEXT,
    application_id INT,
    playlist_id INT,
    playlist_mode VARCHAR(50),
    playlist_name VARCHAR(255),
    error_message TEXT,
    processed TINYINT(1) DEFAULT 0,
    extracted_data TEXT,
    step_details JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    user_id VARCHAR(255),
    is_admin TINYINT(1) DEFAULT 0,
    company_id VARCHAR(255),
    FOREIGN KEY (application_id) REFERENCES application(id),
    FOREIGN KEY (playlist_id) REFERENCES playlists(id)
);

-- Create screenshot_analysis table
CREATE TABLE IF NOT EXISTS screenshot_analysis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    playlist_name VARCHAR(255),
    screenshot_path TEXT,
    openai_result TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create auto_extractor table
CREATE TABLE IF NOT EXISTS auto_extractor (
    id INT AUTO_INCREMENT PRIMARY KEY,
    processed TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX idx_playlists_name ON playlists(name);
CREATE INDEX idx_actions_playlist_id ON actions(playlist_id);
CREATE INDEX idx_direct_uploads_processed ON direct_integration_uploads(processed);
CREATE INDEX idx_direct_uploads_created_at ON direct_integration_uploads(created_at);
CREATE INDEX idx_application_platform ON application(platform);
