-- Meetings: live entity linking ("rich transcripts").
-- meeting_entities = resolved mentions (a property/user/crew spoken in the meeting,
-- matched to a real row) anchored to the transcript by offset_ms. Written by the
-- resolver at live-append time and re-derived from the final transcript at ingest.
-- meeting_entity_alias = the learning table: kind='match' rows teach the resolver
-- company-specific spoken names ("the canes on may" -> property X); kind='block'
-- rows record rejected matches so the same wrong chip never comes back.
-- Live PG < 9.5: plain CREATE INDEX, no ON CONFLICT.

CREATE TABLE IF NOT EXISTS meeting_entities (
    id           varchar(32)  NOT NULL PRIMARY KEY,
    meeting_id   varchar(32)  NOT NULL,
    company      varchar(32)  NOT NULL,
    entity_type  varchar(16)  NOT NULL,            -- property | user | crew | truck
    entity_id    varchar(32)  NOT NULL,
    entity_name  varchar(200),                     -- display name at detection time
    mention_text varchar(200),                     -- the words as heard in the transcript
    offset_ms    integer,                          -- where in the meeting it was said
    confidence   numeric(4,2),
    status       varchar(16)  NOT NULL DEFAULT 'auto',  -- auto | confirmed | rejected
    created      timestamp    NOT NULL DEFAULT now()
);

CREATE INDEX idx_meeting_entities_meeting ON meeting_entities (meeting_id);
CREATE INDEX idx_meeting_entities_entity  ON meeting_entities (entity_type, entity_id);

CREATE TABLE IF NOT EXISTS meeting_entity_alias (
    id          varchar(32)  NOT NULL PRIMARY KEY,
    company     varchar(32)  NOT NULL,
    entity_type varchar(16)  NOT NULL,
    entity_id   varchar(32)  NOT NULL,
    alias       varchar(200) NOT NULL,             -- normalized heard-text
    kind        varchar(8)   NOT NULL DEFAULT 'match',  -- match | block
    created_by  varchar(32),
    created     timestamp    NOT NULL DEFAULT now()
);

CREATE INDEX idx_meeting_entity_alias_company ON meeting_entity_alias (company);
