-- Database schema updates for Enhanced Tilbudgivern Features
-- This script adds tables and columns to support the new package system,
-- advanced geometry calculations, and time calculator features

USE tilbudgivern;

-- Table for storing custom packages
CREATE TABLE IF NOT EXISTS custom_packages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    roof_type ENUM('Betontegl', 'B7', 'B6', 'Vingetegl', 'Røde Teglsten', 'Other') NOT NULL,
    description TEXT,
    materials JSON, -- Store material list with quantities
    base_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    profit_margin DECIMAL(5, 2) NOT NULL DEFAULT 20.00, -- Percentage
    category VARCHAR(100) DEFAULT 'Custom',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table for storing advanced geometry calculations
CREATE TABLE IF NOT EXISTS advanced_geometry_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT,
    height_measurements JSON, -- Store various height calculations
    windboard_data JSON, -- Store windboard calculations
    svg_illustration TEXT, -- Store generated SVG
    material_quantities JSON, -- Store calculated material needs
    complexity_factor DECIMAL(5, 2) DEFAULT 1.0,
    calculation_metadata JSON, -- Store calculation parameters and results
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES customer_projects(id) ON DELETE CASCADE
);

-- Table for storing time calculation results
CREATE TABLE IF NOT EXISTS project_time_calculations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT,
    team_size INT NOT NULL DEFAULT 1,
    base_hours DECIMAL(8, 2) NOT NULL DEFAULT 0.00,
    efficiency_factor DECIMAL(5, 2) NOT NULL DEFAULT 1.0,
    experience_level ENUM('Beginner', 'Experienced', 'Expert') DEFAULT 'Experienced',
    total_calculated_hours DECIMAL(8, 2) NOT NULL DEFAULT 0.00,
    hourly_rate DECIMAL(8, 2) NOT NULL DEFAULT 400.00,
    total_labor_cost DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    time_breakdown JSON, -- Store detailed time breakdown by task
    calculation_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES customer_projects(id) ON DELETE CASCADE
);

-- Add columns to existing customer_projects table for enhanced features
ALTER TABLE customer_projects 
ADD COLUMN IF NOT EXISTS selected_packages JSON,
ADD COLUMN IF NOT EXISTS advanced_geometry_id INT,
ADD COLUMN IF NOT EXISTS time_calculation_id INT,
ADD COLUMN IF NOT EXISTS enhancement_version VARCHAR(50) DEFAULT 'v1.0';

-- Add foreign key constraints for the new relationships
ALTER TABLE customer_projects 
ADD CONSTRAINT fk_advanced_geometry 
FOREIGN KEY (advanced_geometry_id) REFERENCES advanced_geometry_data(id) ON DELETE SET NULL;

ALTER TABLE customer_projects 
ADD CONSTRAINT fk_time_calculation 
FOREIGN KEY (time_calculation_id) REFERENCES project_time_calculations(id) ON DELETE SET NULL;

-- Create indexes for better performance
CREATE INDEX idx_custom_packages_roof_type ON custom_packages(roof_type);
CREATE INDEX idx_custom_packages_category ON custom_packages(category);
CREATE INDEX idx_custom_packages_active ON custom_packages(is_active);
CREATE INDEX idx_advanced_geometry_project ON advanced_geometry_data(project_id);
CREATE INDEX idx_time_calculations_project ON project_time_calculations(project_id);
CREATE INDEX idx_time_calculations_team_size ON project_time_calculations(team_size);

-- Insert default packages for each roof type
INSERT INTO custom_packages (name, roof_type, description, materials, base_price, profit_margin, category) VALUES
('Standard Betontegl Pakke', 'Betontegl', 'Komplet pakke til betontegl installation', 
 JSON_ARRAY(
   JSON_OBJECT('item', 'Betontegl', 'quantity', 25, 'unit', 'm²', 'price', 145),
   JSON_OBJECT('item', 'Lægter 25x50mm', 'quantity', 100, 'unit', 'm', 'price', 12),
   JSON_OBJECT('item', 'Tagpap', 'quantity', 30, 'unit', 'm²', 'price', 35),
   JSON_OBJECT('item', 'Søm og beslag', 'quantity', 1, 'unit', 'sæt', 'price', 250)
 ), 
 4875.00, 25.00, 'Standard'),

('Standard B7 Pakke', 'B7', 'Komplet pakke til B7 tegl installation', 
 JSON_ARRAY(
   JSON_OBJECT('item', 'B7 Tegl', 'quantity', 25, 'unit', 'm²', 'price', 165),
   JSON_OBJECT('item', 'Lægter 25x50mm', 'quantity', 100, 'unit', 'm', 'price', 12),
   JSON_OBJECT('item', 'Tagpap', 'quantity', 30, 'unit', 'm²', 'price', 35),
   JSON_OBJECT('item', 'Søm og beslag', 'quantity', 1, 'unit', 'sæt', 'price', 250)
 ), 
 5375.00, 25.00, 'Standard'),

('Standard B6 Pakke', 'B6', 'Komplet pakke til B6 tegl installation', 
 JSON_ARRAY(
   JSON_OBJECT('item', 'B6 Tegl', 'quantity', 25, 'unit', 'm²', 'price', 155),
   JSON_OBJECT('item', 'Lægter 25x50mm', 'quantity', 100, 'unit', 'm', 'price', 12),
   JSON_OBJECT('item', 'Tagpap', 'quantity', 30, 'unit', 'm²', 'price', 35),
   JSON_OBJECT('item', 'Søm og beslag', 'quantity', 1, 'unit', 'sæt', 'price', 250)
 ), 
 5125.00, 25.00, 'Standard'),

('Standard Vingetegl Pakke', 'Vingetegl', 'Komplet pakke til vingetegl installation', 
 JSON_ARRAY(
   JSON_OBJECT('item', 'Vingetegl', 'quantity', 25, 'unit', 'm²', 'price', 175),
   JSON_OBJECT('item', 'Lægter 32x50mm', 'quantity', 100, 'unit', 'm', 'price', 15),
   JSON_OBJECT('item', 'Tagpap', 'quantity', 30, 'unit', 'm²', 'price', 35),
   JSON_OBJECT('item', 'Søm og beslag', 'quantity', 1, 'unit', 'sæt', 'price', 300)
 ), 
 5675.00, 25.00, 'Standard'),

('Standard Røde Teglsten Pakke', 'Røde Teglsten', 'Komplet pakke til røde teglsten installation', 
 JSON_ARRAY(
   JSON_OBJECT('item', 'Røde Teglsten', 'quantity', 25, 'unit', 'm²', 'price', 185),
   JSON_OBJECT('item', 'Lægter 25x50mm', 'quantity', 100, 'unit', 'm', 'price', 12),
   JSON_OBJECT('item', 'Tagpap Premium', 'quantity', 30, 'unit', 'm²', 'price', 45),
   JSON_OBJECT('item', 'Søm og beslag Premium', 'quantity', 1, 'unit', 'sæt', 'price', 350)
 ), 
 6025.00, 25.00, 'Standard');

-- Create view for easy package selection with calculated prices
CREATE OR REPLACE VIEW package_overview AS
SELECT 
    id,
    name,
    roof_type,
    description,
    category,
    base_price,
    profit_margin,
    ROUND(base_price * (1 + profit_margin / 100), 2) AS selling_price,
    JSON_LENGTH(materials) as material_count,
    is_active,
    created_at
FROM custom_packages
WHERE is_active = TRUE
ORDER BY roof_type, category, name;

-- Create function to calculate total project cost with enhancements
DELIMITER //
CREATE OR REPLACE FUNCTION calculate_enhanced_project_total(project_id INT) 
RETURNS DECIMAL(10,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE total_cost DECIMAL(10,2) DEFAULT 0.00;
    DECLARE package_cost DECIMAL(10,2) DEFAULT 0.00;
    DECLARE labor_cost DECIMAL(10,2) DEFAULT 0.00;
    
    -- Get base project cost (existing calculation)
    SELECT COALESCE(total_price, 0) INTO total_cost 
    FROM customer_projects 
    WHERE id = project_id;
    
    -- Add package costs
    SELECT COALESCE(SUM(
        cp.base_price * (1 + cp.profit_margin / 100)
    ), 0) INTO package_cost
    FROM customer_projects proj
    CROSS JOIN JSON_TABLE(
        COALESCE(proj.selected_packages, JSON_ARRAY()),
        '$[*]' COLUMNS (package_id INT PATH '$.id')
    ) AS pkg
    JOIN custom_packages cp ON cp.id = pkg.package_id
    WHERE proj.id = project_id;
    
    -- Add labor cost from time calculations
    SELECT COALESCE(total_labor_cost, 0) INTO labor_cost
    FROM project_time_calculations 
    WHERE project_id = project_id
    ORDER BY created_at DESC 
    LIMIT 1;
    
    RETURN total_cost + package_cost + labor_cost;
END //
DELIMITER ;

-- Add triggers to keep calculations updated
DELIMITER //
CREATE OR REPLACE TRIGGER update_project_total_on_package_change
AFTER UPDATE ON customer_projects
FOR EACH ROW
BEGIN
    IF NEW.selected_packages != OLD.selected_packages THEN
        UPDATE customer_projects 
        SET total_price = calculate_enhanced_project_total(NEW.id),
            updated_at = CURRENT_TIMESTAMP
        WHERE id = NEW.id;
    END IF;
END //

CREATE OR REPLACE TRIGGER update_project_total_on_time_change
AFTER INSERT ON project_time_calculations
FOR EACH ROW
BEGIN
    UPDATE customer_projects 
    SET total_price = calculate_enhanced_project_total(NEW.project_id),
        updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.project_id;
END //

CREATE OR REPLACE TRIGGER update_project_total_on_time_update
AFTER UPDATE ON project_time_calculations
FOR EACH ROW
BEGIN
    UPDATE customer_projects 
    SET total_price = calculate_enhanced_project_total(NEW.project_id),
        updated_at = CURRENT_TIMESTAMP
    WHERE id = NEW.project_id;
END //
DELIMITER ;

COMMIT;

-- Display summary of changes
SELECT 'Enhanced Tilbudgivern database schema updated successfully!' as Status;
SELECT COUNT(*) as 'Custom Packages Created' FROM custom_packages;
SELECT 'Ready for enhanced features integration' as 'Next Steps';