-- Enhanced Location Schema with Opening Times and Delivery Windows
USE risk_assessment_db;

-- Add opening hours and delivery windows columns to locations table
ALTER TABLE locations 
ADD COLUMN IF NOT EXISTS opening_hours JSON NULL COMMENT 'Opening hours by day of week',
ADD COLUMN IF NOT EXISTS delivery_windows JSON NULL COMMENT 'Delivery time windows by day',
ADD COLUMN IF NOT EXISTS special_instructions TEXT NULL COMMENT 'Special delivery or access instructions',
ADD COLUMN IF NOT EXISTS contact_name VARCHAR(200) NULL,
ADD COLUMN IF NOT EXISTS contact_phone VARCHAR(50) NULL,
ADD COLUMN IF NOT EXISTS contact_email VARCHAR(200) NULL,
ADD COLUMN IF NOT EXISTS parking_info TEXT NULL COMMENT 'Parking and access information',
ADD COLUMN IF NOT EXISTS delivery_restrictions TEXT NULL COMMENT 'Size, weight, vehicle restrictions';

-- Create location_hours table for more detailed scheduling (optional, for future)
CREATE TABLE IF NOT EXISTS location_hours (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    day_of_week TINYINT NOT NULL COMMENT '0=Sunday, 1=Monday, ..., 6=Saturday',
    open_time TIME NULL,
    close_time TIME NULL,
    delivery_start TIME NULL,
    delivery_end TIME NULL,
    is_closed TINYINT(1) DEFAULT 0,
    notes TEXT NULL,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE,
    INDEX idx_location_day (location_id, day_of_week)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create delivery_slots table for specific time windows
CREATE TABLE IF NOT EXISTS delivery_slots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    day_of_week TINYINT NOT NULL,
    slot_start TIME NOT NULL,
    slot_end TIME NOT NULL,
    slot_name VARCHAR(100) NULL COMMENT 'e.g., Morning, Afternoon, Evening',
    max_deliveries INT DEFAULT 1 COMMENT 'Max concurrent deliveries in this slot',
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE,
    INDEX idx_location_day (location_id, day_of_week),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sample data structure for JSON fields
-- opening_hours format:
-- {
--   "monday": {"open": "08:00", "close": "18:00", "closed": false},
--   "tuesday": {"open": "08:00", "close": "18:00", "closed": false},
--   "wednesday": {"open": "08:00", "close": "18:00", "closed": false},
--   "thursday": {"open": "08:00", "close": "18:00", "closed": false},
--   "friday": {"open": "08:00", "close": "18:00", "closed": false},
--   "saturday": {"open": "09:00", "close": "14:00", "closed": false},
--   "sunday": {"open": null, "close": null, "closed": true}
-- }

-- delivery_windows format:
-- {
--   "monday": [{"start": "08:00", "end": "12:00", "name": "Morning"}, {"start": "13:00", "end": "17:00", "name": "Afternoon"}],
--   "tuesday": [{"start": "08:00", "end": "12:00", "name": "Morning"}, {"start": "13:00", "end": "17:00", "name": "Afternoon"}],
--   ...
-- }

-- Insert sample opening hours for existing locations
UPDATE locations 
SET opening_hours = JSON_OBJECT(
    'monday', JSON_OBJECT('open', '08:00', 'close', '18:00', 'closed', false),
    'tuesday', JSON_OBJECT('open', '08:00', 'close', '18:00', 'closed', false),
    'wednesday', JSON_OBJECT('open', '08:00', 'close', '18:00', 'closed', false),
    'thursday', JSON_OBJECT('open', '08:00', 'close', '18:00', 'closed', false),
    'friday', JSON_OBJECT('open', '08:00', 'close', '18:00', 'closed', false),
    'saturday', JSON_OBJECT('open', '09:00', 'close', '14:00', 'closed', false),
    'sunday', JSON_OBJECT('open', NULL, 'close', NULL, 'closed', true)
),
delivery_windows = JSON_OBJECT(
    'monday', JSON_ARRAY(
        JSON_OBJECT('start', '08:00', 'end', '12:00', 'name', 'Morning'),
        JSON_OBJECT('start', '13:00', 'end', '17:00', 'name', 'Afternoon')
    ),
    'tuesday', JSON_ARRAY(
        JSON_OBJECT('start', '08:00', 'end', '12:00', 'name', 'Morning'),
        JSON_OBJECT('start', '13:00', 'end', '17:00', 'name', 'Afternoon')
    ),
    'wednesday', JSON_ARRAY(
        JSON_OBJECT('start', '08:00', 'end', '12:00', 'name', 'Morning'),
        JSON_OBJECT('start', '13:00', 'end', '17:00', 'name', 'Afternoon')
    ),
    'thursday', JSON_ARRAY(
        JSON_OBJECT('start', '08:00', 'end', '12:00', 'name', 'Morning'),
        JSON_OBJECT('start', '13:00', 'end', '17:00', 'name', 'Afternoon')
    ),
    'friday', JSON_ARRAY(
        JSON_OBJECT('start', '08:00', 'end', '12:00', 'name', 'Morning'),
        JSON_OBJECT('start', '13:00', 'end', '17:00', 'name', 'Afternoon')
    ),
    'saturday', JSON_ARRAY(
        JSON_OBJECT('start', '09:00', 'end', '13:00', 'name', 'Morning')
    ),
    'sunday', JSON_ARRAY()
)
WHERE opening_hours IS NULL;

SELECT 'Location enhancements schema created successfully!' as Status;
