-- SQL Schema for leads table (based on save-lead.php requirements)
-- Run this on quiz.wildanet.com server

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

CREATE TABLE IF NOT EXISTS leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Contact Information
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(20),
    
    -- Address Information
    address_line_1 VARCHAR(255),
    address_line_2 VARCHAR(255),
    town VARCHAR(100),
    county VARCHAR(100),
    postcode VARCHAR(10),
    uprn VARCHAR(50),
    
    -- Lead Details
    detected_isp VARCHAR(100),
    lead_source VARCHAR(100) DEFAULT 'one_touch_switch_quiz',
    lead_status ENUM('new', 'contacted', 'qualified', 'converted', 'lost') DEFAULT 'new',
    
    -- Privacy & Consent
    privacy_accepted TINYINT(1) DEFAULT 0,
    consent_ip VARCHAR(45),
    consent_timestamp TIMESTAMP NULL,
    
    -- Additional Information
    notes TEXT,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes
    INDEX idx_email (email),
    INDEX idx_postcode (postcode),
    INDEX idx_lead_status (lead_status),
    INDEX idx_created_at (created_at),
    INDEX idx_lead_source (lead_source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
