-- Sample Data for Boundary Commission Tables
-- This script inserts realistic dummy data for testing

-- Insert Boundaries
INSERT INTO boundary (boundary_number, name, boundary_type, state_a, state_b, length_km, established_date, legal_basis, status, description) VALUES
('BND-001', 'Plateau-Benue Interstate Boundary', 'interstate', 'Plateau State', 'Benue State', 245.5, '1960-10-01', 'Constitutional Act 1960', 'active', 'The official boundary between Plateau and Benue States, established at independence.'),
('BND-002', 'Plateau-Kaduna Interstate Boundary', 'interstate', 'Plateau State', 'Kaduna State', 189.3, '1960-10-01', 'Constitutional Act 1960', 'active', 'Northern boundary of Plateau State with Kaduna State.'),
('BND-003', 'Plateau-Nasarawa Interstate Boundary', 'interstate', 'Plateau State', 'Nasarawa State', 156.8, '1996-10-01', 'State Creation Act 1996', 'active', 'Eastern boundary established during state creation.'),
('BND-004', 'Jos North-Jos South Administrative Boundary', 'administrative', 'Jos North LGA', 'Jos South LGA', 42.5, '1991-05-15', 'Local Government Creation Decree', 'active', 'Administrative boundary within Plateau State.'),
('BND-005', 'Plateau-Cameroon International Boundary', 'international', 'Plateau State', 'Cameroon', 78.2, '1914-01-01', 'Anglo-German Treaty 1914', 'active', 'International boundary section within Plateau State jurisdiction.');

-- Insert Boundary Coordinates (for BND-001)
INSERT INTO boundary_coordinate (boundary_id, sequence, latitude, longitude, elevation, coordinate_system, accuracy, survey_date, survey_method) VALUES
((SELECT id FROM boundary WHERE boundary_number = 'BND-001'), 1, 9.8965, 8.8583, 1250.5, 'WGS84', 2.5, '2020-03-15', 'GPS'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-001'), 2, 9.9123, 8.8756, 1248.2, 'WGS84', 2.3, '2020-03-15', 'GPS'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-001'), 3, 9.9287, 8.8921, 1245.8, 'WGS84', 2.1, '2020-03-15', 'GPS'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-001'), 4, 9.9452, 8.9087, 1243.5, 'WGS84', 2.4, '2020-03-15', 'GPS'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-001'), 5, 9.9618, 8.9254, 1241.2, 'WGS84', 2.2, '2020-03-15', 'GPS');

-- Insert Boundary Coordinates (for BND-002)
INSERT INTO boundary_coordinate (boundary_id, sequence, latitude, longitude, elevation, coordinate_system, accuracy, survey_date, survey_method) VALUES
((SELECT id FROM boundary WHERE boundary_number = 'BND-002'), 1, 10.1234, 8.5234, 1350.0, 'WGS84', 2.0, '2019-11-20', 'GPS'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-002'), 2, 10.1456, 8.5456, 1348.5, 'WGS84', 2.1, '2019-11-20', 'GPS'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-002'), 3, 10.1678, 8.5678, 1347.0, 'WGS84', 2.2, '2019-11-20', 'GPS');

-- Insert Boundary Markers
INSERT INTO boundary_marker (boundary_id, marker_number, marker_type, latitude, longitude, elevation, marker_condition, last_inspection_date, description) VALUES
((SELECT id FROM boundary WHERE boundary_number = 'BND-001'), 'BND-001-MK-001', 'pillar', 9.8965, 8.8583, 1250.5, 'good', '2023-06-15', 'Primary boundary pillar at starting point'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-001'), 'BND-001-MK-002', 'pillar', 9.9287, 8.8921, 1245.8, 'good', '2023-06-15', 'Mid-point boundary pillar'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-001'), 'BND-001-MK-003', 'pillar', 9.9618, 8.9254, 1241.2, 'damaged', '2023-06-15', 'End-point pillar - requires repair'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-002'), 'BND-002-MK-001', 'beacon', 10.1234, 8.5234, 1350.0, 'good', '2023-05-10', 'GPS beacon marker'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-003'), 'BND-003-MK-001', 'monument', 9.7890, 8.4567, 1200.0, 'good', '2023-07-20', 'Historical boundary monument');

-- Insert Boundary Disputes
INSERT INTO boundary_dispute (dispute_number, boundary_id, title, description, status, claimant_a, claimant_b, disputed_area_km2, filed_date, assigned_officer, priority) VALUES
('DSP-001', (SELECT id FROM boundary WHERE boundary_number = 'BND-001'), 'Plateau-Benue Land Dispute at Wase Area', 'Dispute over 15.5 km² of land along the Plateau-Benue boundary near Wase Local Government Area. Both states claim ownership of the disputed territory.', 'under_investigation', 'Plateau State', 'Benue State', 15.5, '2023-01-15', 'Officer John Doe', 'high'),
('DSP-002', (SELECT id FROM boundary WHERE boundary_number = 'BND-004'), 'Jos North-South Administrative Boundary Dispute', 'Ongoing dispute regarding the exact location of the administrative boundary between Jos North and Jos South LGAs, affecting several communities.', 'mediation', 'Jos North LGA', 'Jos South LGA', 3.2, '2023-03-20', 'Officer Jane Smith', 'normal'),
('DSP-003', NULL, 'General Interstate Boundary Clarification', 'Request for clarification on multiple boundary points between Plateau and neighboring states. No specific area disputed.', 'pending', 'Plateau State', 'Multiple States', NULL, '2023-05-10', NULL, 'low');

-- Insert Dispute Evidence
INSERT INTO dispute_evidence (dispute_id, document_type, title, description, submitted_by, submitted_date, is_primary) VALUES
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-001'), 'survey', '2020 Boundary Survey Report - Wase Area', 'Comprehensive survey report documenting the boundary coordinates in the disputed area.', 'Survey Team Alpha', '2023-01-20', TRUE),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-001'), 'map', 'Historical Map 1950', 'Historical map showing boundary location from 1950.', 'Plateau State Archives', '2023-01-25', FALSE),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-001'), 'treaty', '1960 Constitutional Boundary Agreement', 'Original constitutional document establishing state boundaries.', 'National Archives', '2023-02-01', TRUE),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-002'), 'survey', '2022 Administrative Boundary Survey', 'Recent survey of administrative boundaries in Jos area.', 'Survey Team Beta', '2023-03-25', TRUE),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-002'), 'testimony', 'Community Leader Testimonies', 'Collection of testimonies from community leaders on both sides.', 'Mediation Committee', '2023-04-10', FALSE);

-- Insert Boundary Surveys
INSERT INTO boundary_survey (boundary_id, survey_number, survey_date, survey_team, survey_method, accuracy, notes, approved, approved_by, approved_date) VALUES
((SELECT id FROM boundary WHERE boundary_number = 'BND-001'), 'SUR-2020-001', '2020-03-15', 'Survey Team Alpha', 'GPS', 2.3, 'Complete boundary survey using modern GPS technology. All coordinates verified.', TRUE, 'Director Survey', '2020-04-01'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-002'), 'SUR-2019-045', '2019-11-20', 'Survey Team Beta', 'GPS', 2.1, 'Routine boundary verification survey.', TRUE, 'Director Survey', '2019-12-05'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-003'), 'SUR-2023-012', '2023-07-20', 'Survey Team Gamma', 'Satellite', 1.8, 'High-resolution satellite survey for boundary clarification.', FALSE, NULL, NULL);

-- Insert Boundary Treaties
INSERT INTO boundary_treaty (boundary_id, treaty_number, title, signing_date, effective_date, parties, summary, status) VALUES
((SELECT id FROM boundary WHERE boundary_number = 'BND-001'), 'TRT-1960-001', 'Plateau-Benue Boundary Agreement 1960', '1960-10-01', '1960-10-01', 'Plateau State,Benue State', 'Original agreement establishing the interstate boundary at independence.', 'active'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-005'), 'TRT-1914-INT-001', 'Anglo-German Boundary Treaty 1914', '1914-01-01', '1914-01-01', 'United Kingdom,Germany', 'International treaty establishing Nigeria-Cameroon boundary.', 'active'),
((SELECT id FROM boundary WHERE boundary_number = 'BND-003'), 'TRT-1996-001', 'State Creation Boundary Agreement 1996', '1996-10-01', '1996-10-01', 'Plateau State,Nasarawa State', 'Agreement establishing boundaries during state creation in 1996.', 'active');

-- Insert Stakeholders
INSERT INTO stakeholder (name, entity_type, contact_person, email, phone, address, role, notes) VALUES
('Plateau State Government', 'state', 'Commissioner for Lands', 'lands@plateaustate.gov.ng', '+234-803-123-4567', 'Jos, Plateau State', 'claimant', 'Primary stakeholder in boundary matters'),
('Benue State Government', 'state', 'Commissioner for Lands', 'lands@benuestate.gov.ng', '+234-803-234-5678', 'Makurdi, Benue State', 'claimant', 'Primary stakeholder in boundary matters'),
('Wase Community Leaders', 'community', 'Chief Wase', 'chief@wasecommunity.ng', '+234-803-345-6789', 'Wase, Plateau State', 'affected_party', 'Community directly affected by boundary dispute'),
('Jos North LGA', 'lga', 'LGA Chairman', 'chairman@josnorthlga.ng', '+234-803-456-7890', 'Jos North, Plateau State', 'claimant', 'Local government involved in administrative boundary'),
('Jos South LGA', 'lga', 'LGA Chairman', 'chairman@jossouthlga.ng', '+234-803-567-8901', 'Jos South, Plateau State', 'claimant', 'Local government involved in administrative boundary'),
('National Boundary Commission', 'organization', 'Director General', 'info@boundarycommission.gov.ng', '+234-803-678-9012', 'Abuja, FCT', 'mediator', 'Federal agency responsible for boundary management');

-- Link Stakeholders to Disputes
INSERT INTO dispute_stakeholder (dispute_id, stakeholder_id, role_in_dispute) VALUES
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-001'), (SELECT id FROM stakeholder WHERE name = 'Plateau State Government'), 'claimant'),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-001'), (SELECT id FROM stakeholder WHERE name = 'Benue State Government'), 'claimant'),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-001'), (SELECT id FROM stakeholder WHERE name = 'Wase Community Leaders'), 'affected_party'),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-001'), (SELECT id FROM stakeholder WHERE name = 'National Boundary Commission'), 'mediator'),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-002'), (SELECT id FROM stakeholder WHERE name = 'Jos North LGA'), 'claimant'),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-002'), (SELECT id FROM stakeholder WHERE name = 'Jos South LGA'), 'claimant'),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-002'), (SELECT id FROM stakeholder WHERE name = 'National Boundary Commission'), 'mediator');

-- Insert Dispute Timeline Events
INSERT INTO dispute_timeline (dispute_id, event_date, event_type, description, actor) VALUES
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-001'), '2023-01-15 10:00:00', 'filed', 'Dispute case filed by Plateau State Government', 'Plateau State Government'),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-001'), '2023-01-20 14:30:00', 'evidence_submitted', 'Initial survey evidence submitted', 'Survey Team Alpha'),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-001'), '2023-02-15 09:00:00', 'status_change', 'Status changed to Under Investigation', 'Officer John Doe'),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-002'), '2023-03-20 11:00:00', 'filed', 'Administrative boundary dispute filed', 'Jos North LGA'),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-002'), '2023-04-01 10:00:00', 'status_change', 'Status changed to Mediation', 'Officer Jane Smith'),
((SELECT id FROM boundary_dispute WHERE dispute_number = 'DSP-002'), '2023-04-15 14:00:00', 'mediation_meeting', 'First mediation meeting held', 'National Boundary Commission');