-- Document Signing System Database Schema - ENHANCED VERSION
-- Original tables PLUS new sales workflow features

-- Users/Admin Table
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user') DEFAULT 'user',
    garage_name VARCHAR(255),
    garage_logo_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Customers Table (NEW - separate from admin users)
CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    postcode VARCHAR(10),
    customer_type ENUM('individual', 'business') DEFAULT 'individual',
    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,
    INDEX idx_user_email (user_id, email),
    INDEX idx_created_date (created_at)
);

-- Sales Table (NEW - tracks vehicle sales)
CREATE TABLE IF NOT EXISTS sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    customer_id INT NOT NULL,
    vehicle_registration VARCHAR(20) NOT NULL,
    vehicle_make VARCHAR(100),
    vehicle_model VARCHAR(100),
    vehicle_year INT,
    vehicle_colour VARCHAR(50),
    vehicle_vin VARCHAR(50),
    sale_type ENUM('part_exchange_in', 'part_exchange_out', 'direct_sale', 'finance', 'warranty', 'service') DEFAULT 'direct_sale',
    sale_price DECIMAL(10, 2),
    sale_status ENUM('draft', 'pending_signatures', 'complete', 'archived', 'disputed') DEFAULT 'draft',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    created_ip VARCHAR(45),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_user_status (user_id, sale_status),
    INDEX idx_customer_sales (customer_id),
    INDEX idx_vehicle_reg (vehicle_registration),
    INDEX idx_created_date (created_at)
);

-- Signature Requirements Table (NEW - mandatory signatures per sale type)
CREATE TABLE IF NOT EXISTS signature_requirements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    sale_type VARCHAR(50) NOT NULL,
    document_type VARCHAR(100) NOT NULL,
    sequence_number INT DEFAULT 1,
    is_mandatory BOOLEAN DEFAULT TRUE,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_type (user_id, sale_type),
    UNIQUE KEY unique_requirement (user_id, sale_type, document_type)
);

-- Sale Documents Table (NEW - links documents to sales)
CREATE TABLE IF NOT EXISTS sale_documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sale_id INT NOT NULL,
    document_id INT NOT NULL,
    document_type VARCHAR(100),
    sequence_number INT DEFAULT 1,
    is_required BOOLEAN DEFAULT TRUE,
    signed_status ENUM('pending', 'signed', 'waived') DEFAULT 'pending',
    signed_at TIMESTAMP NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    INDEX idx_sale_status (sale_id, signed_status),
    INDEX idx_document_sale (document_id)
);

-- Document Templates Table
CREATE TABLE IF NOT EXISTS document_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    template_name VARCHAR(255) NOT NULL,
    document_type ENUM('warranty', 'service_agreement', 'finance_agreement', 'purchase_agreement', 'contract', 'handover_form', 'distance_sale', 'disclaimer', 'part_exchange', 'trade_in', 'other') NOT NULL,
    template_content LONGTEXT NOT NULL,
    footer_text TEXT,
    signature_fields JSON,
    is_mandatory BOOLEAN DEFAULT TRUE,
    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,
    INDEX idx_user_template (user_id, document_type)
);

-- Documents Table
CREATE TABLE IF NOT EXISTS documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    sale_id INT,
    template_id INT,
    customer_id INT,
    document_type VARCHAR(100) NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(100) NOT NULL,
    customer_phone VARCHAR(20),
    vehicle_info VARCHAR(255),
    document_data JSON NOT NULL,
    status ENUM('draft', 'pending_signature', 'signed', 'archived') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    signed_at TIMESTAMP NULL,
    created_ip VARCHAR(45),
    signed_ip VARCHAR(45),
    document_hash VARCHAR(256),
    pdf_path VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (template_id) REFERENCES document_templates(id) ON DELETE SET NULL,
    INDEX idx_user_status (user_id, status),
    INDEX idx_customer_email (customer_email),
    INDEX idx_sale_documents (sale_id),
    INDEX idx_created_date (created_at),
    INDEX idx_document_hash (document_hash)
);

-- Signatures Table
CREATE TABLE IF NOT EXISTS signatures (
    id INT PRIMARY KEY AUTO_INCREMENT,
    document_id INT NOT NULL,
    signer_name VARCHAR(255) NOT NULL,
    signer_email VARCHAR(100),
    signer_role ENUM('customer', 'admin', 'witness') DEFAULT 'customer',
    signature_data LONGTEXT NOT NULL,
    signature_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    signer_ip VARCHAR(45),
    signature_hash VARCHAR(256),
    device_info VARCHAR(255),
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
    INDEX idx_document_signer (document_id, signer_role)
);

-- Email Log Table
CREATE TABLE IF NOT EXISTS email_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    document_id INT,
    sale_id INT,
    recipient_email VARCHAR(100) NOT NULL,
    recipient_type ENUM('customer', 'admin') NOT NULL,
    email_type ENUM('signature_request', 'signed_confirmation', 'reminder', 'admin_notification') DEFAULT 'signature_request',
    subject VARCHAR(255) NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
    error_message TEXT,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL,
    INDEX idx_document_log (document_id),
    INDEX idx_sale_log (sale_id),
    INDEX idx_sent_date (sent_at),
    INDEX idx_email_status (status)
);

-- Reminders Table (NEW - tracks follow-up reminders)
CREATE TABLE IF NOT EXISTS reminders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sale_id INT NOT NULL,
    document_id INT,
    reminder_type ENUM('signature_pending', 'document_missing', 'sale_incomplete') DEFAULT 'signature_pending',
    reminder_text VARCHAR(255),
    scheduled_for TIMESTAMP,
    sent_at TIMESTAMP NULL,
    status ENUM('pending', 'sent', 'cancelled') DEFAULT 'pending',
    attempt_count INT DEFAULT 0,
    max_attempts INT DEFAULT 3,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
    INDEX idx_sale_reminders (sale_id),
    INDEX idx_scheduled (scheduled_for, status),
    INDEX idx_status (status)
);

-- Audit Log Table
CREATE TABLE IF NOT EXISTS audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    document_id INT,
    sale_id INT,
    action VARCHAR(100) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL,
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL,
    INDEX idx_action_date (action, created_at),
    INDEX idx_document_audit (document_id),
    INDEX idx_sale_audit (sale_id)
);

-- Create additional indexes for common queries
CREATE INDEX idx_user_created ON documents(user_id, created_at DESC);
CREATE INDEX idx_status_date ON documents(status, created_at DESC);
CREATE INDEX idx_sale_created ON sales(user_id, created_at DESC);
CREATE INDEX idx_sale_status_date ON sales(sale_status, created_at DESC);
CREATE INDEX idx_customer_created ON customers(user_id, created_at DESC);
