-- Support physical patch-panel layouts such as 1A, 1B … 24A, 24B.
ALTER TABLE locations_cross_fields
    ADD COLUMN IF NOT EXISTS port_label_format VARCHAR(20) NOT NULL DEFAULT 'numeric',
    ADD COLUMN IF NOT EXISTS panel_row_size INTEGER NOT NULL DEFAULT 24;

ALTER TABLE locations_cross_fields
    DROP CONSTRAINT IF EXISTS locations_cross_fields_port_label_format_check;
ALTER TABLE locations_cross_fields
    ADD CONSTRAINT locations_cross_fields_port_label_format_check
    CHECK (port_label_format IN ('numeric', 'paired'));

ALTER TABLE locations_cross_fields
    DROP CONSTRAINT IF EXISTS locations_cross_fields_panel_row_size_check;
ALTER TABLE locations_cross_fields
    ADD CONSTRAINT locations_cross_fields_panel_row_size_check
    CHECK (panel_row_size BETWEEN 1 AND 48);

-- Port labels are physical labels, not necessarily numbers (for example 1A/1B).
ALTER TABLE locations_cross_field_ports
    DROP CONSTRAINT IF EXISTS locations_cross_field_ports_port_number_check;

ALTER TABLE locations_cross_field_ports
    ALTER COLUMN port_number TYPE VARCHAR(20) USING port_number::VARCHAR;

ALTER TABLE locations_cross_field_ports
    ADD COLUMN IF NOT EXISTS port_order INTEGER;

UPDATE locations_cross_field_ports
SET port_order = CASE
    WHEN port_number ~ '^[0-9]+$' THEN port_number::INTEGER
    ELSE id
END
WHERE port_order IS NULL;

ALTER TABLE locations_cross_field_ports
    ALTER COLUMN port_order SET NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_cross_field_ports_field_order
    ON locations_cross_field_ports(cross_field_id, port_order);