-- Migration: Create sub_locations table for granular location tracking
-- Description: Adds sub-location support (suite, room, area, zone, bay, aisle) within main locations
-- Date: 2026-01-18

-- Create sub_locations table
CREATE TABLE IF NOT EXISTS sub_locations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_location_id INT UNSIGNED NOT NULL,
    code VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('suite', 'room', 'area', 'zone', 'bay', 'aisle') NOT NULL DEFAULT 'room',
    description TEXT,
    square_footage DECIMAL(10,2) COMMENT 'Square footage of the sub-location',
    status ENUM('active', 'inactive') DEFAULT 'active',
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    UNIQUE KEY unique_code_per_location (parent_location_id, code),
    KEY idx_parent_location (parent_location_id),
    KEY idx_status (status),
    KEY idx_deleted_at (deleted_at),
    CONSTRAINT fk_sub_location_parent FOREIGN KEY (parent_location_id) 
        REFERENCES locations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add sub_location_types dropdown to system_settings (for display in Settings > Dropdowns)
INSERT INTO system_settings (setting_key, setting_value, setting_type, description)
VALUES (
    'sub_location_types',
    '{"suite":"Suite","room":"Room","area":"Area","zone":"Zone","bay":"Bay","aisle":"Aisle"}',
    'dropdown',
    'Types of sub-locations within a location'
) ON DUPLICATE KEY UPDATE 
    setting_value = '{"suite":"Suite","room":"Room","area":"Area","zone":"Zone","bay":"Bay","aisle":"Aisle"}',
    description = 'Types of sub-locations within a location';