-- Create whistleblowing_reports table for whistleblowing policy reports
CREATE TABLE IF NOT EXISTS whistleblowing_reports (
    id SERIAL PRIMARY KEY,
    reference_number VARCHAR(20) UNIQUE NOT NULL,
    
    -- Report Details
    concern_type VARCHAR(100) NOT NULL,
    concern_category VARCHAR(100) NOT NULL,
    concern_description TEXT NOT NULL,
    incident_date DATE,
    ongoing_issue BOOLEAN DEFAULT FALSE,
    
    -- Parties Involved
    individuals_involved TEXT,
    departments_involved TEXT,
    external_parties_involved TEXT,
    
    -- Evidence and Documentation
    evidence_available BOOLEAN DEFAULT FALSE,
    evidence_description TEXT,
    documents_attached BOOLEAN DEFAULT FALSE,
    witnesses_available BOOLEAN DEFAULT FALSE,
    witness_details TEXT,
    
    -- Reporter Information (Optional)
    reporter_name VARCHAR(255),
    reporter_position VARCHAR(255),
    reporter_department VARCHAR(255),
    reporter_contact_phone VARCHAR(20),
    reporter_contact_email VARCHAR(255),
    anonymous_report BOOLEAN DEFAULT TRUE,
    
    -- Previous Reporting
    previously_reported BOOLEAN DEFAULT FALSE,
    previous_report_details TEXT,
    previous_report_outcome TEXT,
    
    -- Urgency and Risk Assessment
    urgency_level VARCHAR(20) NOT NULL,
    risk_level VARCHAR(20) DEFAULT 'medium',
    immediate_action_required BOOLEAN DEFAULT FALSE,
    potential_harm_description TEXT,
    
    -- Investigation Preferences
    preferred_investigation_method VARCHAR(100),
    external_investigation_preferred BOOLEAN DEFAULT FALSE,
    confidentiality_level VARCHAR(50) DEFAULT 'high',
    
    -- Case Management
    status VARCHAR(20) DEFAULT 'received',
    assigned_investigator VARCHAR(255),
    investigation_start_date DATE,
    target_completion_date DATE,
    actual_completion_date DATE,
    
    -- Follow-up and Communication
    reporter_feedback_requested BOOLEAN DEFAULT FALSE,
    communication_method VARCHAR(50),
    updates_provided INTEGER DEFAULT 0,
    case_notes TEXT,
    
    -- Outcomes and Actions
    investigation_outcome TEXT,
    corrective_actions_taken TEXT,
    preventive_measures_implemented TEXT,
    policy_changes_recommended TEXT,
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    acknowledged_at TIMESTAMP,
    closed_at TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_whistleblowing_reference_number ON whistleblowing_reports (reference_number);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_concern_type ON whistleblowing_reports (concern_type);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_urgency ON whistleblowing_reports (urgency_level);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_status ON whistleblowing_reports (status);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_created_at ON whistleblowing_reports (created_at);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_anonymous ON whistleblowing_reports (anonymous_report);
CREATE INDEX IF NOT EXISTS idx_whistleblowing_risk_level ON whistleblowing_reports (risk_level);

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

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

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

-- Create function to update timestamp and set target completion date
CREATE OR REPLACE FUNCTION update_whistleblowing_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := CURRENT_TIMESTAMP;
    
    -- Set target completion date if investigation starts
    IF OLD.investigation_start_date IS NULL AND NEW.investigation_start_date IS NOT NULL THEN
        NEW.target_completion_date := NEW.investigation_start_date + INTERVAL '30 days';
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger to update timestamp
DROP TRIGGER IF EXISTS whistleblowing_update_trigger ON whistleblowing_reports;
CREATE TRIGGER whistleblowing_update_trigger
    BEFORE UPDATE ON whistleblowing_reports
    FOR EACH ROW
    EXECUTE FUNCTION update_whistleblowing_timestamp();

-- Add comments for documentation
COMMENT ON TABLE whistleblowing_reports IS 'Stores whistleblowing reports from the Whistleblowing Policy page';
COMMENT ON COLUMN whistleblowing_reports.reference_number IS 'Auto-generated reference number in format WB-YYYY-NNNN';
COMMENT ON COLUMN whistleblowing_reports.concern_type IS 'Type of concern: fraud, harassment, safety, compliance, etc.';
COMMENT ON COLUMN whistleblowing_reports.confidentiality_level IS 'Level of confidentiality required: low, medium, high';
COMMENT ON COLUMN whistleblowing_reports.target_completion_date IS 'Auto-set to 30 days from investigation start';
