-- Create incident_reports table for anonymous incident reporting
CREATE TABLE IF NOT EXISTS incident_reports (
    id SERIAL PRIMARY KEY,
    reference_number VARCHAR(20) UNIQUE NOT NULL,
    
    -- Incident Details
    incident_type VARCHAR(100) NOT NULL,
    incident_description TEXT NOT NULL,
    incident_date DATE,
    incident_time TIME,
    incident_location TEXT,
    
    -- Parties Involved
    victim_age_group VARCHAR(50),
    victim_gender VARCHAR(20),
    perpetrator_description TEXT,
    witnesses_present BOOLEAN DEFAULT FALSE,
    
    -- Reporting Details
    reporter_relationship VARCHAR(100),
    reporter_contact_method VARCHAR(50),
    reporter_phone VARCHAR(20),
    reporter_email VARCHAR(255),
    anonymous_report BOOLEAN DEFAULT TRUE,
    
    -- Urgency and Follow-up
    urgency_level VARCHAR(20) NOT NULL,
    immediate_danger BOOLEAN DEFAULT FALSE,
    authorities_contacted BOOLEAN DEFAULT FALSE,
    which_authorities TEXT,
    
    -- Additional Information
    additional_details TEXT,
    supporting_evidence TEXT,
    
    -- Consent and Privacy
    consent_to_contact BOOLEAN DEFAULT FALSE,
    consent_to_investigate BOOLEAN DEFAULT TRUE,
    
    -- Case Management
    status VARCHAR(20) DEFAULT 'pending',
    assigned_officer VARCHAR(255),
    priority_level VARCHAR(20) DEFAULT 'medium',
    follow_up_required BOOLEAN DEFAULT TRUE,
    case_notes TEXT,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_incident_reference_number ON incident_reports (reference_number);
CREATE INDEX IF NOT EXISTS idx_incident_type ON incident_reports (incident_type);
CREATE INDEX IF NOT EXISTS idx_incident_urgency ON incident_reports (urgency_level);
CREATE INDEX IF NOT EXISTS idx_incident_status ON incident_reports (status);
CREATE INDEX IF NOT EXISTS idx_incident_date ON incident_reports (incident_date);
CREATE INDEX IF NOT EXISTS idx_incident_created_at ON incident_reports (created_at);
CREATE INDEX IF NOT EXISTS idx_incident_immediate_danger ON incident_reports (immediate_danger);

-- Create sequence for reference number generation
CREATE SEQUENCE IF NOT EXISTS incident_ref_seq START 1;

-- Create function to generate reference numbers
CREATE OR REPLACE FUNCTION generate_incident_reference()
RETURNS TRIGGER AS $$
BEGIN
    NEW.reference_number := 'VF-' || EXTRACT(YEAR FROM CURRENT_DATE) || '-' || LPAD(nextval('incident_ref_seq')::TEXT, 4, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to auto-generate reference numbers
DROP TRIGGER IF EXISTS incident_reference_trigger ON incident_reports;
CREATE TRIGGER incident_reference_trigger
    BEFORE INSERT ON incident_reports
    FOR EACH ROW
    EXECUTE FUNCTION generate_incident_reference();

-- Create function to update timestamp
CREATE OR REPLACE FUNCTION update_incident_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to update timestamp
DROP TRIGGER IF EXISTS incident_update_trigger ON incident_reports;
CREATE TRIGGER incident_update_trigger
    BEFORE UPDATE ON incident_reports
    FOR EACH ROW
    EXECUTE FUNCTION update_incident_timestamp();

-- Add comments for documentation
COMMENT ON TABLE incident_reports IS 'Stores anonymous incident reports from the Report Incident page';
COMMENT ON COLUMN incident_reports.reference_number IS 'Auto-generated reference number in format VF-YYYY-NNNN';
COMMENT ON COLUMN incident_reports.anonymous_report IS 'Whether this is an anonymous report';
COMMENT ON COLUMN incident_reports.immediate_danger IS 'Whether there is immediate danger requiring urgent response';
