-- Create database
CREATE DATABASE IF NOT EXISTS car_quote_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE car_quote_db;

-- Main form submissions table
CREATE TABLE IF NOT EXISTS form_submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Personal Information
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(100),
    zip_code VARCHAR(10),
    
    -- Birth Information
    birth_month VARCHAR(2),
    birth_day VARCHAR(2),
    birth_year VARCHAR(4),
    
    -- Driver Information
    gender VARCHAR(10),
    is_home_owner VARCHAR(3),
    is_married VARCHAR(3),
    served_in_military VARCHAR(3),
    
    -- Education & Credit
    education VARCHAR(50),
    credit_score VARCHAR(20),
    
    -- Driving History
    us_valid_driver_license VARCHAR(3),
    ticket_or_claim_last_three_years VARCHAR(3),
    sr22 VARCHAR(3),
    
    -- Insurance Details
    is_currently_insured VARCHAR(3),
    current_insurance_carrier VARCHAR(100),
    current_insurance_coverage VARCHAR(50),
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    INDEX idx_email (email),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vehicles table (one-to-many relationship)
CREATE TABLE IF NOT EXISTS form_vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    form_submission_id INT NOT NULL,
    
    -- Vehicle Information
    car_make VARCHAR(50),
    car_year VARCHAR(4),
    car_model VARCHAR(100),
    primary_use VARCHAR(50),
    annual_mileage VARCHAR(50),
    coverage_level VARCHAR(50),
    ownership VARCHAR(50),
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (form_submission_id) REFERENCES form_submissions(id) ON DELETE CASCADE,
    INDEX idx_form_submission (form_submission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Contact form submissions table
CREATE TABLE IF NOT EXISTS contact_submissions (
    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,
    phone VARCHAR(20) NOT NULL,
    comments TEXT NOT NULL,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    INDEX idx_email (email),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Privacy rights requests table
CREATE TABLE IF NOT EXISTS privacy_rights_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Personal Information
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    zip_code VARCHAR(10) NOT NULL,
    
    -- Request Types (each can be selected or not)
    request_restrict_processing TINYINT(1) DEFAULT 0,
    request_correct_data TINYINT(1) DEFAULT 0,
    request_delete_data TINYINT(1) DEFAULT 0,
    request_confirm_data TINYINT(1) DEFAULT 0,
    request_receive_copy TINYINT(1) DEFAULT 0,
    
    -- Consumer Status
    is_consumer VARCHAR(10) NOT NULL,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    INDEX idx_email (email),
    INDEX idx_created_at (created_at),
    INDEX idx_zip (zip_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;