-- Boundary Commission Database Schema
-- Additional tables for boundary management, disputes, and interstate coordination

-- Main boundary table
CREATE TABLE IF NOT EXISTS boundary (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    boundary_number VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    boundary_type ENUM('interstate', 'international', 'administrative', 'customary', 'disputed') NOT NULL,
    state_a VARCHAR(100) NOT NULL,
    state_b VARCHAR(100) NOT NULL,
    length_km DECIMAL(10, 2),
    established_date DATE,
    legal_basis TEXT,
    status VARCHAR(50) DEFAULT 'active',
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_boundary_number (boundary_number),
    INDEX idx_boundary_type (boundary_type),
    INDEX idx_states (state_a, state_b),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Boundary coordinates (GIS data)
CREATE TABLE IF NOT EXISTS boundary_coordinate (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    boundary_id BIGINT NOT NULL,
    sequence INT NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    elevation DECIMAL(8, 2),
    coordinate_system VARCHAR(50) DEFAULT 'WGS84',
    accuracy DECIMAL(8, 2),
    survey_date DATE,
    survey_method VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (boundary_id) REFERENCES boundary(id) ON DELETE CASCADE,
    INDEX idx_boundary_id (boundary_id),
    INDEX idx_sequence (boundary_id, sequence),
    INDEX idx_coordinates (latitude, longitude)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Physical boundary markers
CREATE TABLE IF NOT EXISTS boundary_marker (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    boundary_id BIGINT NOT NULL,
    marker_number VARCHAR(50) NOT NULL,
    marker_type ENUM('pillar', 'beacon', 'monument', 'natural', 'gps_coordinate') NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    elevation DECIMAL(8, 2),
    marker_condition VARCHAR(50),
    last_inspection_date DATE,
    description TEXT,
    photos TEXT,  -- Comma-separated URLs
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (boundary_id) REFERENCES boundary(id) ON DELETE CASCADE,
    UNIQUE KEY unique_marker (boundary_id, marker_number),
    INDEX idx_boundary_id (boundary_id),
    INDEX idx_marker_type (marker_type),
    INDEX idx_coordinates (latitude, longitude)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Boundary disputes
CREATE TABLE IF NOT EXISTS boundary_dispute (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dispute_number VARCHAR(50) UNIQUE NOT NULL,
    boundary_id BIGINT,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    status ENUM('pending', 'under_investigation', 'mediation', 'adjudication', 'resolved', 'appealed', 'closed') DEFAULT 'pending',
    claimant_a VARCHAR(100) NOT NULL,
    claimant_b VARCHAR(100) NOT NULL,
    disputed_area_km2 DECIMAL(10, 2),
    filed_date DATE NOT NULL,
    resolution_date DATE,
    resolution_summary TEXT,
    legal_reference VARCHAR(255),
    assigned_officer VARCHAR(100),
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (boundary_id) REFERENCES boundary(id) ON DELETE SET NULL,
    INDEX idx_dispute_number (dispute_number),
    INDEX idx_boundary_id (boundary_id),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_claimants (claimant_a, claimant_b),
    INDEX idx_filed_date (filed_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dispute evidence
CREATE TABLE IF NOT EXISTS dispute_evidence (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dispute_id BIGINT NOT NULL,
    document_type VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    document_url VARCHAR(500),
    file_path VARCHAR(500),
    submitted_by VARCHAR(100),
    submitted_date DATE NOT NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (dispute_id) REFERENCES boundary_dispute(id) ON DELETE CASCADE,
    INDEX idx_dispute_id (dispute_id),
    INDEX idx_document_type (document_type),
    INDEX idx_is_primary (is_primary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Boundary surveys
CREATE TABLE IF NOT EXISTS boundary_survey (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    boundary_id BIGINT NOT NULL,
    survey_number VARCHAR(50) NOT NULL,
    survey_date DATE NOT NULL,
    survey_team VARCHAR(255),
    survey_method VARCHAR(100) NOT NULL,
    accuracy DECIMAL(8, 2),
    report_url VARCHAR(500),
    notes TEXT,
    approved BOOLEAN DEFAULT FALSE,
    approved_by VARCHAR(100),
    approved_date DATE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (boundary_id) REFERENCES boundary(id) ON DELETE CASCADE,
    UNIQUE KEY unique_survey (boundary_id, survey_number),
    INDEX idx_boundary_id (boundary_id),
    INDEX idx_survey_date (survey_date),
    INDEX idx_approved (approved)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Boundary treaties and agreements
CREATE TABLE IF NOT EXISTS boundary_treaty (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    boundary_id BIGINT,
    treaty_number VARCHAR(50) UNIQUE NOT NULL,
    title VARCHAR(255) NOT NULL,
    signing_date DATE NOT NULL,
    effective_date DATE,
    parties TEXT NOT NULL,  -- Comma-separated list
    document_url VARCHAR(500),
    summary TEXT,
    status VARCHAR(50) DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (boundary_id) REFERENCES boundary(id) ON DELETE SET NULL,
    INDEX idx_treaty_number (treaty_number),
    INDEX idx_boundary_id (boundary_id),
    INDEX idx_status (status),
    INDEX idx_signing_date (signing_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Extended stakeholders (for disputes)
CREATE TABLE IF NOT EXISTS stakeholder (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    entity_type VARCHAR(100) NOT NULL,  -- state, lga, community, organization
    contact_person VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    address TEXT,
    role VARCHAR(100),
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_entity_type (entity_type),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Junction table for dispute-stakeholder relationships
CREATE TABLE IF NOT EXISTS dispute_stakeholder (
    dispute_id BIGINT NOT NULL,
    stakeholder_id BIGINT NOT NULL,
    role_in_dispute VARCHAR(100),  -- claimant, respondent, witness, etc.
    PRIMARY KEY (dispute_id, stakeholder_id),
    FOREIGN KEY (dispute_id) REFERENCES boundary_dispute(id) ON DELETE CASCADE,
    FOREIGN KEY (stakeholder_id) REFERENCES stakeholder(id) ON DELETE CASCADE,
    INDEX idx_dispute_id (dispute_id),
    INDEX idx_stakeholder_id (stakeholder_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dispute timeline/events
CREATE TABLE IF NOT EXISTS dispute_timeline (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    dispute_id BIGINT NOT NULL,
    event_date DATETIME NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    description TEXT,
    actor VARCHAR(100),
    document_url VARCHAR(500),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (dispute_id) REFERENCES boundary_dispute(id) ON DELETE CASCADE,
    INDEX idx_dispute_id (dispute_id),
    INDEX idx_event_date (event_date),
    INDEX idx_event_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;