-- Migration: Add Vehicle Types and Delivery Volumes Reference Tables
-- This makes vehicle types and delivery volumes configurable instead of hardcoded

USE scubatricky_risk;

-- Create vehicle_types table
CREATE TABLE IF NOT EXISTS vehicle_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert default vehicle types
INSERT INTO vehicle_types (type_name, description, display_order) VALUES
('Small Van', 'Small delivery van (e.g., Transit Connect, Caddy)', 1),
('Large Van', 'Large panel van (e.g., Sprinter, Transit)', 2),
('7.5 Tonne', '7.5 tonne rigid vehicle', 3),
('Rigid Truck', 'Rigid truck (18-26 tonne)', 4),
('Articulated Lorry', 'Articulated lorry/HGV', 5),
('Box Truck', 'Box truck with tail lift', 6),
('Flatbed', 'Flatbed truck', 7)
ON DUPLICATE KEY UPDATE type_name = type_name;

-- Create delivery_volumes table
CREATE TABLE IF NOT EXISTS delivery_volumes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    volume_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert default delivery volumes
INSERT INTO delivery_volumes (volume_name, description, display_order) VALUES
('Small (1-10 items)', 'Small delivery, few items', 1),
('Medium (11-50 items)', 'Medium delivery', 2),
('Large (51-100 items)', 'Large delivery', 3),
('Very Large (100+ items)', 'Very large or full load', 4),
('Full Pallet', 'Full pallet delivery', 5),
('Multiple Pallets', 'Multiple pallets', 6)
ON DUPLICATE KEY UPDATE volume_name = volume_name;

-- Verify tables created
SELECT 'Vehicle Types:' as info, COUNT(*) as count FROM vehicle_types;
SELECT 'Delivery Volumes:' as info, COUNT(*) as count FROM delivery_volumes;

SELECT 'Migration completed successfully!' AS status;
