-- TIME Phase T3: search projection and representation materialization foundation for time entries.

CREATE TABLE IF NOT EXISTS "time".time_entry_search_projection (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    time_entry_id UUID NOT NULL UNIQUE REFERENCES "time".time_entry(id) ON DELETE CASCADE,
    document_id UUID NOT NULL UNIQUE REFERENCES DOC.doc_document(id) ON DELETE CASCADE,
    source_system "time".time_source_system NOT NULL,
    external_id VARCHAR(255) NOT NULL,
    language_code VARCHAR(16),
    entry_start TIMESTAMP WITH TIME ZONE,
    entry_end TIMESTAMP WITH TIME ZONE,
    duration_seconds BIGINT,
    person_external_id VARCHAR(255),
    person_display_name VARCHAR(255),
    activity_type_id INTEGER,
    activity_type_code VARCHAR(64),
    activity_type_name VARCHAR(255),
    record_type VARCHAR(64),
    time_recording_dbk VARCHAR(24),
    time_recording_mcl_id VARCHAR(255),
    time_recording_desc VARCHAR(255),
    time_recording_remark VARCHAR(255),
    time_recording_url VARCHAR(1000),
    primary_task_dbk VARCHAR(24),
    primary_task_mcl_id VARCHAR(255),
    primary_task_name VARCHAR(255),
    primary_task_desc TEXT,
    primary_cost_unit_dbk VARCHAR(24),
    primary_cost_unit_mcl_id VARCHAR(255),
    primary_cost_unit_name VARCHAR(255),
    primary_cost_unit_desc VARCHAR(255),
    primary_contract_dbk VARCHAR(24),
    primary_contract_name VARCHAR(255),
    primary_contract_iref VARCHAR(255),
    primary_contract_position_dbk VARCHAR(24),
    primary_contract_position_name VARCHAR(255),
    primary_contract_position_iref VARCHAR(255),
    primary_organization_dbk VARCHAR(24),
    primary_organization_name VARCHAR(255),
    task_ids_text TEXT,
    task_names_text TEXT,
    task_descriptions_text TEXT,
    cost_unit_ids_text TEXT,
    cost_unit_names_text TEXT,
    cost_unit_descriptions_text TEXT,
    contract_names_text TEXT,
    contract_irefs_text TEXT,
    contract_position_names_text TEXT,
    contract_position_irefs_text TEXT,
    organization_names_text TEXT,
    semantic_text TEXT,
    summary_text TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_time_entry_search_projection_document ON "time".time_entry_search_projection(document_id);
CREATE INDEX IF NOT EXISTS idx_time_entry_search_projection_source ON "time".time_entry_search_projection(source_system, external_id);
CREATE INDEX IF NOT EXISTS idx_time_entry_search_projection_entry_start ON "time".time_entry_search_projection(entry_start DESC);
CREATE INDEX IF NOT EXISTS idx_time_entry_search_projection_person ON "time".time_entry_search_projection(person_external_id);
CREATE INDEX IF NOT EXISTS idx_time_entry_search_projection_activity_type ON "time".time_entry_search_projection(activity_type_id);
