-- Migration: Add PDF Template System
-- Allows custom PDF report templates with field mapping

USE scubatricky_risk;

-- PDF template definitions
CREATE TABLE IF NOT EXISTS pdf_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(200) NOT NULL UNIQUE,
    description TEXT,
    template_type ENUM('assessment', 'location', 'summary', 'custom') DEFAULT 'assessment',
    page_size ENUM('A4', 'Letter', 'Legal', 'A3') DEFAULT 'A4',
    orientation ENUM('portrait', 'landscape') DEFAULT 'portrait',
    template_config JSON,  -- Complete template configuration
    is_default BOOLEAN DEFAULT 0,
    is_active BOOLEAN DEFAULT 1,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_type (template_type),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- Template configuration structure (stored in template_config JSON):
-- {
--   "header": {
--     "enabled": true,
--     "logo": "path/to/logo.png",
--     "title": "Risk Assessment Report",
--     "company_name": "Your Company",
--     "height": 60
--   },
--   "footer": {
--     "enabled": true,
--     "left_text": "Confidential",
--     "center_text": "Page {page} of {total}",
--     "right_text": "{date}",
--     "height": 40
--   },
--   "sections": [
--     {
--       "type": "text",
--       "label": "Assessment Details",
--       "fontSize": 16,
--       "bold": true,
--       "color": "#333333"
--     },
--     {
--       "type": "field",
--       "source": "assessments",
--       "field": "assessment_number",
--       "label": "Assessment Number",
--       "fontSize": 12
--     },
--     {
--       "type": "table",
--       "source": "assessment_details",
--       "columns": ["risk_item", "likelihood", "severity", "risk_score"],
--       "headers": ["Risk Item", "Likelihood", "Severity", "Score"]
--     },
--     {
--       "type": "image",
--       "source": "photos",
--       "showCaption": true,
--       "maxWidth": 400,
--       "maxHeight": 300
--     }
--   ],
--   "styling": {
--     "margins": {"top": 20, "right": 20, "bottom": 20, "left": 20},
--     "fontFamily": "Helvetica",
--     "primaryColor": "#2563eb",
--     "secondaryColor": "#64748b"
--   }
-- }

-- Insert default template
INSERT INTO pdf_templates (
    template_name, 
    description, 
    template_type, 
    template_config,
    is_default,
    created_by
) VALUES (
    'Standard Assessment Report',
    'Default comprehensive assessment report with all details',
    'assessment',
    '{"header":{"enabled":true,"title":"Risk Assessment Report","company_name":"Risk System","height":60},"footer":{"enabled":true,"center_text":"Page {page} of {total}","height":40},"sections":[{"type":"heading","text":"Assessment Information","level":1},{"type":"field","source":"assessments","field":"assessment_number","label":"Assessment Number"},{"type":"field","source":"assessments","field":"assessment_date","label":"Assessment Date"},{"type":"field","source":"locations","field":"location_name","label":"Location"},{"type":"heading","text":"Risk Evaluation","level":1},{"type":"table","source":"assessment_details","columns":["risk_item","likelihood","severity","risk_score","current_controls"],"headers":["Risk Item","Likelihood","Severity","Score","Current Controls"]}],"styling":{"margins":{"top":20,"right":20,"bottom":20,"left":20},"fontFamily":"Helvetica","primaryColor":"#2563eb"}}',
    1,
    1
);

-- Available field mapping reference
CREATE TABLE IF NOT EXISTS pdf_field_mapping (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    field_name VARCHAR(100) NOT NULL,
    field_label VARCHAR(200) NOT NULL,
    field_type ENUM('text', 'number', 'date', 'datetime', 'boolean', 'json') DEFAULT 'text',
    description TEXT,
    is_active BOOLEAN DEFAULT 1,
    display_order INT DEFAULT 0,
    UNIQUE KEY unique_table_field (table_name, field_name)
) ENGINE=InnoDB;

-- Populate field mappings
INSERT INTO pdf_field_mapping (table_name, field_name, field_label, field_type, display_order) VALUES
-- Assessments table
('assessments', 'assessment_number', 'Assessment Number', 'text', 1),
('assessments', 'assessment_date', 'Assessment Date', 'date', 2),
('assessments', 'assessment_time', 'Assessment Time', 'text', 3),
('assessments', 'weather_conditions', 'Weather Conditions', 'text', 4),
('assessments', 'vehicle_type', 'Vehicle Type', 'text', 5),
('assessments', 'delivery_volume', 'Delivery Volume', 'text', 6),
('assessments', 'overall_risk_level', 'Overall Risk Level', 'text', 7),
('assessments', 'status', 'Status', 'text', 8),
('assessments', 'notes', 'Notes', 'text', 9),
('assessments', 'recommendations', 'Recommendations', 'text', 10),

-- Locations table
('locations', 'location_name', 'Location Name', 'text', 11),
('locations', 'address', 'Address', 'text', 12),
('locations', 'city', 'City', 'text', 13),
('locations', 'county', 'County', 'text', 14),
('locations', 'postcode', 'Postcode', 'text', 15),
('locations', 'latitude', 'Latitude', 'number', 16),
('locations', 'longitude', 'Longitude', 'number', 17),
('locations', 'what3words', 'What3Words Address', 'text', 18),
('locations', 'contact_name', 'Contact Name', 'text', 19),
('locations', 'contact_phone', 'Contact Phone', 'text', 20),
('locations', 'contact_email', 'Contact Email', 'text', 21),

-- Users table
('users', 'full_name', 'Assessor Name', 'text', 22),
('users', 'email', 'Assessor Email', 'text', 23),
('users', 'username', 'Assessor Username', 'text', 24),

-- Assessment Details (risk items)
('assessment_details', 'likelihood', 'Likelihood Rating', 'number', 25),
('assessment_details', 'severity', 'Severity Rating', 'number', 26),
('assessment_details', 'risk_score', 'Risk Score', 'number', 27),
('assessment_details', 'current_controls', 'Current Controls', 'text', 28),
('assessment_details', 'additional_actions', 'Additional Actions', 'text', 29),
('assessment_details', 'action_owner', 'Action Owner', 'text', 30),
('assessment_details', 'target_date', 'Target Date', 'date', 31),

-- Risk Items
('risk_items', 'item_name', 'Risk Item Name', 'text', 32),
('risk_items', 'description', 'Risk Item Description', 'text', 33),

-- Risk Categories
('risk_categories', 'category_name', 'Risk Category', 'text', 34),
('risk_categories', 'description', 'Category Description', 'text', 35),

-- Photos
('photos', 'caption', 'Photo Caption', 'text', 36),
('photos', 'uploaded_at', 'Photo Upload Date', 'datetime', 37);

SELECT 'PDF Template System migration completed!' AS status;
SELECT COUNT(*) as template_count FROM pdf_templates;
SELECT COUNT(*) as field_count FROM pdf_field_mapping;
