-- =====================================================
-- Contact Form Pro - Database Schema v7.4.4.2
-- Run this SQL in phpMyAdmin or MySQL command line
-- =====================================================

CREATE DATABASE IF NOT EXISTS payfyexc_contactformpro CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE payfyexc_contactformpro;

-- Extension users table (machine_id + device_id for unique identification)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    machine_id VARCHAR(255) NOT NULL,
    device_id VARCHAR(64) DEFAULT NULL,
    login_key VARCHAR(100) NOT NULL,
    nickname VARCHAR(100) DEFAULT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    allow_concurrent BOOLEAN DEFAULT FALSE,
    max_concurrent INT DEFAULT 2,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_seen TIMESTAMP NULL,
    last_ip VARCHAR(45) NULL,
    notes TEXT NULL,
    UNIQUE KEY unique_machine_device (machine_id, device_id),
    INDEX idx_machine_id (machine_id),
    INDEX idx_device_id (device_id),
    INDEX idx_login_key (login_key)
) ENGINE=InnoDB;

-- User sessions
CREATE TABLE IF NOT EXISTS sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_token VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    is_valid BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_session_token (session_token),
    INDEX idx_user_id (user_id),
    INDEX idx_ip_address (ip_address)
) ENGINE=InnoDB;

-- Concurrent login attempts log
CREATE TABLE IF NOT EXISTS concurrent_login_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    machine_id VARCHAR(255) NOT NULL,
    original_ip VARCHAR(45) NOT NULL,
    blocked_ip VARCHAR(45) NOT NULL,
    original_session_id INT NULL,
    blocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_agent TEXT,
    was_allowed BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_blocked_at (blocked_at)
) ENGINE=InnoDB;

-- Activity logs (detailed tracking)
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_id INT NULL,
    action_type ENUM('success', 'skip', 'stay', 'no_form', 'error', 'captcha', 'custom', 'tab_closed', 'tab_opened') NOT NULL,
    company_name VARCHAR(255) NULL,
    website_url TEXT NULL,
    status VARCHAR(100) NULL,
    redirect_url TEXT NULL,
    filled_fields TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_action_type (action_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- Daily statistics (aggregated for faster reporting)
CREATE TABLE IF NOT EXISTS daily_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    stat_date DATE NOT NULL,
    success_count INT DEFAULT 0,
    skip_count INT DEFAULT 0,
    stay_count INT DEFAULT 0,
    no_form_count INT DEFAULT 0,
    error_count INT DEFAULT 0,
    captcha_count INT DEFAULT 0,
    custom_count INT DEFAULT 0,
    tab_closed_count INT DEFAULT 0,
    tab_opened_count INT DEFAULT 0,
    total_processed INT DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_date (user_id, stat_date),
    INDEX idx_stat_date (stat_date)
) ENGINE=InnoDB;

-- =====================================================
-- Migration SQL for existing databases (v7.4.4.1 -> v7.4.4.2)
-- Run these if upgrading from previous version
-- =====================================================
-- ALTER TABLE users ADD COLUMN allow_concurrent BOOLEAN DEFAULT FALSE AFTER is_active;
-- ALTER TABLE users ADD COLUMN max_concurrent INT DEFAULT 2 AFTER allow_concurrent;
-- ALTER TABLE sessions ADD INDEX idx_ip_address (ip_address);
-- 
-- CREATE TABLE IF NOT EXISTS concurrent_login_logs (
--     id INT AUTO_INCREMENT PRIMARY KEY,
--     user_id INT NOT NULL,
--     machine_id VARCHAR(255) NOT NULL,
--     original_ip VARCHAR(45) NOT NULL,
--     blocked_ip VARCHAR(45) NOT NULL,
--     original_session_id INT NULL,
--     blocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
--     user_agent TEXT,
--     was_allowed BOOLEAN DEFAULT FALSE,
--     FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
--     INDEX idx_user_id (user_id),
--     INDEX idx_blocked_at (blocked_at)
-- ) ENGINE=InnoDB;
