-- Boundary Commission Document Storage Schema
-- Stores scanned documents, evidence, treaties, and survey reports

-- Boundary documents table (similar to lr_source_document)
CREATE TABLE IF NOT EXISTS boundary_document (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    document_number VARCHAR(50) UNIQUE NOT NULL,
    -- Optional secondary/pseudo document number used in legacy systems or physical registries
    pseudo_document_number VARCHAR(50) NULL,
    document_type ENUM('boundary_file', 'administrative_file') NOT NULL,
    related_boundary_id BIGINT,
    related_dispute_id BIGINT,
    related_survey_id BIGINT,
    related_treaty_id BIGINT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    content_path VARCHAR(500),  -- Path to file in contentstore (e.g., "boundary/2024/01/15/file.bin")
    file_name VARCHAR(255),
    file_size BIGINT,
    mime_type VARCHAR(100),
    page_count INT DEFAULT 0,
    uploaded_by VARCHAR(100),
    uploaded_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT 'active',
    metadata JSON,  -- Additional metadata as JSON
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (related_boundary_id) REFERENCES boundary(id) ON DELETE SET NULL,
    FOREIGN KEY (related_dispute_id) REFERENCES boundary_dispute(id) ON DELETE SET NULL,
    FOREIGN KEY (related_survey_id) REFERENCES boundary_survey(id) ON DELETE SET NULL,
    FOREIGN KEY (related_treaty_id) REFERENCES boundary_treaty(id) ON DELETE SET NULL,
    INDEX idx_document_number (document_number),
    INDEX idx_pseudo_document_number (pseudo_document_number),
    INDEX idx_document_type (document_type),
    INDEX idx_boundary_id (related_boundary_id),
    INDEX idx_dispute_id (related_dispute_id),
    INDEX idx_uploaded_date (uploaded_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Update dispute_evidence to link to boundary_document
ALTER TABLE dispute_evidence 
ADD COLUMN document_id BIGINT NULL AFTER id,
ADD FOREIGN KEY (document_id) REFERENCES boundary_document(id) ON DELETE SET NULL,
ADD INDEX idx_document_id (document_id);

-- Update boundary_survey to link to boundary_document
ALTER TABLE boundary_survey 
ADD COLUMN report_document_id BIGINT NULL AFTER report_url,
ADD FOREIGN KEY (report_document_id) REFERENCES boundary_document(id) ON DELETE SET NULL,
ADD INDEX idx_report_document_id (report_document_id);

-- Update boundary_treaty to link to boundary_document
ALTER TABLE boundary_treaty 
ADD COLUMN treaty_document_id BIGINT NULL AFTER document_url,
ADD FOREIGN KEY (treaty_document_id) REFERENCES boundary_document(id) ON DELETE SET NULL,
ADD INDEX idx_treaty_document_id (treaty_document_id);