-- Form Management System Database Schema
-- Add these tables to your topche10_document_signing database

-- Categories table (for your 35 form categories)
CREATE TABLE IF NOT EXISTS form_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Form Templates table (individual forms within categories)
CREATE TABLE IF NOT EXISTS form_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    form_name VARCHAR(255) NOT NULL,
    description TEXT,
    form_fields JSON, -- Stores field definitions as JSON
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES form_categories(id) ON DELETE CASCADE,
    UNIQUE KEY unique_form (category_id, form_name)
);

-- Extended Customers table (for CRM/upselling)
CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    address VARCHAR(500),
    postcode VARCHAR(20),
    phone VARCHAR(20),
    vehicle_reg VARCHAR(50),
    vehicle_make VARCHAR(100),
    vehicle_model VARCHAR(100),
    vehicle_vin VARCHAR(100),
    notes TEXT,
    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 (user_id, customer_name),
    INDEX (postcode)
);

-- Generated Documents table (track PDFs created)
CREATE TABLE IF NOT EXISTS generated_documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    customer_id INT,
    template_id INT NOT NULL,
    pdf_filename VARCHAR(255),
    pdf_path VARCHAR(500),
    form_data JSON, -- Stores the filled form data
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (template_id) REFERENCES form_templates(id) ON DELETE CASCADE,
    INDEX (user_id, generated_at),
    INDEX (customer_id)
);

-- Insert your 35 form categories
INSERT INTO form_categories (name, description, display_order) VALUES
('Buying vehicle into stock forms', 'Forms for acquiring vehicles into inventory', 1),
('Seller declaration forms', 'Seller declaration and verification forms', 2),
('Vehicle appraisal and condition forms', 'Vehicle assessment and condition documentation', 3),
('Part-exchange forms', 'Part-exchange transaction forms', 4),
('Outstanding finance settlement forms', 'Finance settlement forms', 5),
('Mileage declaration forms', 'Mileage verification and declaration', 6),
('Vehicle history / HPI check records', 'Vehicle history and HPI check records', 7),
('V5C / DVLA transfer records', 'DVLA V5C transfer documentation', 8),
('Stock book and VAT margin records', 'Stock and VAT margin tracking', 9),
('Customer quotation forms', 'Customer quotation and estimate forms', 10),
('Sales order / purchase agreement forms', 'Sales agreements and purchase orders', 11),
('Deposit and reservation forms', 'Deposit and reservation documentation', 12),
('Final invoice and payment receipt forms', 'Invoice and payment receipt forms', 13),
('Vehicle description forms', 'Vehicle description and specification forms', 14),
('Pre-delivery inspection forms', 'Pre-delivery vehicle inspection', 15),
('Handover and collection forms', 'Vehicle handover and collection forms', 16),
('Delivery forms', 'Vehicle delivery documentation', 17),
('Distance selling and cancellation forms', 'Distance selling and cancellation compliance', 18),
('Terms and conditions forms', 'Terms and conditions documents', 19),
('Finance disclosure forms', 'Finance disclosure forms', 20),
('Finance application and consent forms', 'Finance application forms', 21),
('Credit check consent forms', 'Credit check consent forms', 22),
('Commission disclosure forms', 'Commission disclosure forms', 23),
('Warranty forms', 'Warranty documentation', 24),
('Warranty upgrade forms', 'Warranty upgrade forms', 25),
('Repair and goodwill agreement forms', 'Repair and goodwill agreements', 26),
('Courtesy car forms', 'Courtesy car provision forms', 27),
('Complaint and dispute forms', 'Complaint and dispute resolution forms', 28),
('Refund and cancellation forms', 'Refund and cancellation forms', 29),
('Customer ID and proof of address forms', 'Customer identification forms', 30),
('Anti-money laundering / source of funds forms', 'AML and source of funds verification', 31),
('Privacy and data protection forms', 'GDPR and privacy forms', 32),
('Marketing consent forms', 'Marketing consent documentation', 33),
('Internal prep and repair tracking forms', 'Internal vehicle prep tracking', 34),
('Manager approval and deal approval forms', 'Manager approval forms', 35),
('Sold vehicle checklist forms', 'Post-sale checklist forms', 36);
