-- =============================================================================
-- 000_map_layers_corrections.sql
-- Pre-migration corrections to public.map_layers:
--   1. Title correction for DB layer_id 32
--   2. New vector layer records required before junction table population
--
-- Run this before any other migration files.
-- Wayleave vector layers are NOT included here — define those separately
-- once layer_names, geometry types and editability are confirmed.
-- =============================================================================


-- -----------------------------------------------------------------------------
-- 1. Title correction
-- -----------------------------------------------------------------------------

UPDATE public.map_layers
SET layer_title = 'CFL OLT Locations (Cabinet)'
WHERE layer_id = 32;


-- -----------------------------------------------------------------------------
-- 2. New vector layer records
--
-- Note on stocklistedit ProjectBoundary:
--   The existing DB layer_id=18 (layer_name=ProjectBoundary) points to
--   projects.projects — correct for projectedit.
--   stocklistedit uses a different view (stocklists.stocklist_projects_geom)
--   so it gets its own record: layer_name=StocklistProjectBoundary.
--   The stocklistedit JS layerName will be updated to match during the port.
--
-- Note on Plan Network:
--   Commented out in project_edit_v2.js — not inserted here.
-- -----------------------------------------------------------------------------

INSERT INTO public.map_layers (
    layer_title,
    layer_name,
    wms_layer_name,
    tooltip,
    layer_type,
    layer_category,
    interact_type,
    interact_group,
    toggle_min_zoom,
    source_min_zoom,
    source_max_zoom,
    url,
    z_index,
    visible,
    layer_interactive,
    vector_style,
    vector_style_field,
    label_field,
    render_mode,
    focus_on_load,
    layer_editable,
    layer_is_edited,
    layer_allow_create,
    uid,
    snap_to_this,
    attribute_form,
    editable_fields,
    database_schema,
    database_table,
    layer_active
)
VALUES

-- projectedit: Project Neighbours
(
    'Project Neighbours',
    'projectNeighbours',
    '',
    'Nearby projects up to 10km away, for visibility only',
    'vectorPoly',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    'fn/map_get_v2.php?geotable=projects.project_nearest_neighbours&geomfield=geom',
    10,
    true,
    true,
    'grey',
    '',
    'project_name',
    '',
    false,
    false, false, false,
    '',
    false,
    '',
    '[]',
    'projects',
    'project_nearest_neighbours',
    true
),

-- projectedit: Plan Equipment
(
    'Plan Equipment',
    'PlanEquipment',
    '',
    'Use this layer to place splitters or other network equipment',
    'vectorPoint',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    'fn/map_get_v2.php?geotable=prospector.network_points&geomfield=geom',
    23,
    true,
    false,
    'PlanEquipmentstyles',
    'point_type',
    'point_name',
    '',
    false,
    true, false, true,
    'point_id',
    true,
    'PlanEquipmentAttributeForm',
    '[{"name":"Equipment Name/Reference","id":"attributeForm-equipment-point_name","property":"point_name","input_type":"text"},{"name":"Customer ID/Reference","id":"attributeForm-equipment-customer_point_id","property":"customer_point_id","input_type":"text"},{"name":"Point Type","id":"attributeForm-equipment-point_type","property":"point_type","input_type":"dropdown","dropdown_options":[{"name":"Select Type","value":""},{"name":"Meet Me Point","value":"MM"},{"name":"L0 - Cab","value":"L0"},{"name":"L1 - DJ/SN","value":"DJ"},{"name":"Splitter 1:32","value":"1:32"},{"name":"Splitter 1:8","value":"1:8"},{"name":"Splitter 1:4","value":"1:4"},{"name":"CMJ","value":"CMJ"},{"name":"CMJ (1:4)","value":"CMJ(1:4)"},{"name":"CMJ (1:8)","value":"CMJ(1:8)"},{"name":"CMJ(1:4-1:8)","value":"CMJ(1:4-1:8)"},{"name":"FDN","value":"FDN"},{"name":"PBO","value":"PBO"}]},{"name":"PIANOI Reference","id":"attributeForm-equipment-pianoi_ref","property":"pianoi_ref","input_type":"text"},{"name":"Address","id":"attributeForm-point_address","property":"point_address","input_type":"text"},{"name":"Fibre Allocation Details","id":"attributeForm-point_fibre_allocation_details","property":"point_fibre_allocation_details","input_type":"textarea"}]',
    'prospector',
    'network_points',
    true
),

-- projectedit: Plan Structures
(
    'Plan Structures',
    'PlanStructures',
    '',
    'Use this layer to place enclosures like cabinets, splitter boxes or chambers',
    'vectorPoint',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    'fn/map_get_v2.php?geotable=prospector.network_structures&geomfield=geom',
    22,
    true,
    false,
    'PlanStructurestyles',
    'structure_type',
    'structure_name',
    '',
    false,
    true, false, true,
    'structure_id',
    true,
    'PlanStructuresAttributeForm',
    '[{"name":"Structure Name/Reference","id":"attributeForm-structures-structure_name","property":"structure_name","input_type":"text"},{"name":"Customer ID/Reference","id":"attributeForm-structures-customer_structure_id","property":"customer_structure_id","input_type":"text"},{"name":"Structure Type","id":"attributeForm-structures-structure_type","property":"structure_type","input_type":"dropdown","dropdown_options":[{"name":"Select Type","value":""},{"name":"Cab","value":"CAB"},{"name":"Chamber/Jointing Box","value":"JB"},{"name":"Splitter Box","value":"SB"}]}]',
    'prospector',
    'network_structures',
    true
),

-- projectedit: Plan Cables
(
    'Plan Cables',
    'PlanCables',
    '',
    'Use this layer to place fibers/cables',
    'vectorLine',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    'fn/map_get_v2.php?geotable=prospector.network_cables&geomfield=geom_user',
    20,
    true,
    false,
    'autoPlanNetworkStyles',
    'cable_type',
    'cable_ref',
    '',
    false,
    true, false, true,
    'cable_id',
    true,
    'PlanCablesAttributeForm',
    '[{"name":"Cable Name/Reference","id":"attributeForm-cable-cable_ref","property":"cable_ref","input_type":"text"},{"name":"Cable Status","id":"attributeForm-blockages-cable_status","property":"cable_status","input_type":"dropdown","dropdown_options":[{"name":"Select Type","value":""},{"name":"Planned","value":"Planned"},{"name":"Surveyed","value":"Surveyed"},{"name":"Built","value":"Built"}]},{"name":"Customer ID/Reference","id":"attributeForm-cable-customer_cable_id","property":"customer_cable_id","input_type":"text"},{"name":"Cable Type","id":"attributeForm-cable-cable_type","property":"cable_type","input_type":"dropdown","dropdown_options":[{"name":"Select Type","value":""},{"name":"1F","value":"1F"},{"name":"2F","value":"2F"},{"name":"4F","value":"4F"},{"name":"8F","value":"8F"},{"name":"12F","value":"12F"},{"name":"24F","value":"24F"},{"name":"48F","value":"48F"},{"name":"96F","value":"96F"},{"name":"144F","value":"144F"}]},{"name":"Cable A-End","id":"attributeForm-cable-a_end","property":"a_end","input_type":"dropdown","dropdown_options_layer":"PlanEquipment","dropdown_options":[{"name":"No connection","value":"0"}]},{"name":"Cable B-End","id":"attributeForm-cable-b_end","property":"b_end","input_type":"dropdown","dropdown_options_layer":"PlanEquipment","dropdown_options":[{"name":"No connection","value":"0"}]},{"name":"Fibre Allocation","id":"attributeForm-fibre_allocation","property":"fibre_allocation","input_type":"text"},{"name":"PIANOI Reference","id":"attributeForm-cable-pianoi_ref","property":"pianoi_ref","input_type":"text"}]',
    'prospector',
    'network_cables',
    true
),

-- projectedit: Plan Duct
(
    'Plan Duct',
    'PlanDuct',
    '',
    'Use this layer to place ducting/civils',
    'vectorLine',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    'fn/map_get_v2.php?geotable=prospector.network_duct&geomfield=geom_user',
    14,
    true,
    false,
    'autoPlanNetworkStyles',
    'duct_type',
    'duct_ref',
    '',
    false,
    true, false, true,
    'duct_id',
    true,
    'PlanDuctAttributeForm',
    '[{"name":"Duct Name/Reference","id":"attributeForm-duct-duct_ref","property":"duct_ref","input_type":"text"},{"name":"Customer Duct ID/Reference","id":"attributeForm-duct-customer_duct_id","property":"customer_duct_id","input_type":"text"},{"name":"Duct Type","id":"attributeForm-duct-duct_type","property":"duct_type","input_type":"dropdown","dropdown_options":[{"name":"Select Type","value":""},{"name":"1 Way","value":"1W"},{"name":"2 Way","value":"2W"},{"name":"3 Way","value":"3W"},{"name":"4 Way","value":"4W"}]}]',
    'prospector',
    'network_duct',
    true
),

-- projectedit: Plan SubDuct
(
    'Plan SubDuct',
    'PlanSubDuct',
    '',
    'Use this layer to place subducting/civils',
    'vectorLine',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    'fn/map_get_v2.php?geotable=prospector.network_subduct&geomfield=geom_user',
    15,
    true,
    false,
    'autoPlanNetworkStyles',
    'subduct_type',
    'subduct_ref',
    '',
    false,
    true, false, true,
    'subduct_id',
    true,
    'PlanSubDuctAttributeForm',
    '[{"name":"SubDuct Name/Reference","id":"attributeForm-subduct-subduct_ref","property":"subduct_ref","input_type":"text"},{"name":"Customer SubDuct ID/Reference","id":"attributeForm-subduct-customer_subduct_id","property":"customer_subduct_id","input_type":"text"},{"name":"SubDuct Type","id":"attributeForm-subduct-subduct_type","property":"subduct_type","input_type":"dropdown","dropdown_options":[{"name":"Select Type","value":""},{"name":"12:8 Mono","value":"12:8"},{"name":"6W Combi","value":"6W"},{"name":"7W Combi","value":"7W"},{"name":"7mm/3.5mm","value":"7mm/3.5mm"},{"name":"Other","value":"Oth"}]},{"name":"PIANOI Reference","id":"attributeForm-subduct-pianoi_ref","property":"pianoi_ref","input_type":"text"}]',
    'prospector',
    'network_subduct',
    true
),

-- projectedit: PIA Blockages (editable vector, distinct from reference WMS layer DB 24)
(
    'PIA Blockages',
    'projectPIABlockages',
    '',
    'Specify the location of a blockage on the PIA network as part of this project',
    'vectorPoly',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    'fn/map_get_v2.php?geotable=prospector.pia_blockages&geomfield=geom',
    11,
    true,
    false,
    'piaBlockageStyles',
    'blockage_status',
    'blockage_status',
    '',
    false,
    true, false, true,
    'blockage_id',
    false,
    'projectPIABlockagesAttributeForm',
    '[{"name":"Blockage Type","id":"attributeForm-blockages-blockage_type","property":"blockage_type","input_type":"dropdown","dropdown_options":[{"name":"Select Type","value":""},{"name":"Public","value":"Public"},{"name":"Private","value":"Private"}]},{"name":"Blockage Status","id":"attributeForm-blockages-blockage_status","property":"blockage_status","input_type":"dropdown","dropdown_options":[{"name":"Select Type","value":""},{"name":"Active","value":"Active"},{"name":"Resolved","value":"Resolved"}]}]',
    'prospector',
    'pia_blockages',
    true
),

-- stocklistedit + accountedit: Stocklist Premises (entity layer)
(
    'Stocklist Premises',
    'StocklistPremises',
    '',
    '',
    'vectorPoint',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    'fn/map_get_v2.php?geotable=stocklists.stocklist_premises_geom&geomfield=geom',
    11,
    true,
    false,
    'PlanEquipmentstyles',
    'style_type',
    '',
    '',
    true,
    false, false, false,
    '',
    false,
    '',
    '[]',
    'stocklists',
    'stocklist_premises_geom',
    true
),

-- stocklistedit: Project Boundary (stocklist view — different data source from DB 18)
-- JS will be updated from layerName=ProjectBoundary to StocklistProjectBoundary during port
(
    'Project Boundary (Stocklist)',
    'StocklistProjectBoundary',
    '',
    'Project boundaries associated with this stocklist',
    'vectorPoly',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    'fn/map_get_v2.php?geotable=stocklists.stocklist_projects_geom&geomfield=geom',
    11,
    true,
    false,
    'blue',
    '',
    'project_name',
    '',
    false,
    false, false, false,
    '',
    false,
    'ProjectBoundaryAttributeForm',
    '[]',
    'stocklists',
    'stocklist_projects_geom',
    true
),

-- opportunityedit: Opportunity Projects
(
    'Opportunity Projects',
    'opportunityProjects',
    '',
    '',
    'vectorPolyDynamic',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    '',
    11,
    true,
    false,
    'opportunityPolyStyles',
    'project_type',
    'project_name',
    '',
    true,
    false, false, false,
    '',
    false,
    '',
    '[]',
    '',
    '',
    true
),

-- opportunityedit: Opportunity Routes
(
    'Opportunity Routes',
    'opportunityRoutes',
    '',
    '',
    'vectorLineDynamic',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    'fn/map_get_v2.php?geotable=prospector.vw_opportunity_routes&geomfield=geom',
    11,
    true,
    false,
    'opportunityLineStyles',
    'route_type',
    '',
    '',
    true,
    false, false, false,
    '',
    false,
    '',
    '[]',
    'prospector',
    'vw_opportunity_routes',
    true
),

-- opportunityedit: Opportunity Equipment
(
    'Opportunity Equipment',
    'vw_opportunity_network_points',
    '',
    '',
    'vectorPoint',
    'project',
    'checkbox',
    'project',
    0, 0, 30,
    'fn/map_get_v2.php?geotable=prospector.vw_opportunity_network_points&geomfield=geom',
    11,
    true,
    false,
    'PlanEquipmentstyles',
    'point_type',
    'point_name',
    '',
    false,
    false, false, false,
    '',
    false,
    '',
    '[]',
    'prospector',
    'vw_opportunity_network_points',
    true
);


-- -----------------------------------------------------------------------------
-- Verify: list all project-category layers after insert
-- -----------------------------------------------------------------------------
 SELECT layer_id, layer_name, layer_title, layer_type, layer_active
 FROM public.map_layers
 WHERE layer_category = 'project'
 ORDER BY layer_id;
