Helpdesk and ticketing

CREATE TYPE ticket_status AS ENUM ('new', 'open', 'pending', 'solved', 'closed');

CREATE TYPE ticket_priority AS ENUM ('low', 'normal', 'high', 'urgent');

CREATE TABLE agents (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text NOT NULL,
    active boolean DEFAULT true NOT NULL
);

CREATE TABLE customers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text
);

CREATE TABLE tickets (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id uuid NOT NULL,
    assignee_id uuid,
    subject text NOT NULL,
    status ticket_status DEFAULT 'new' NOT NULL,
    priority ticket_priority DEFAULT 'normal' NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL,
    solved_at timestamptz
);
CREATE INDEX tickets_status_idx ON tickets (status, priority);
CREATE INDEX tickets_customer_id_idx ON tickets (customer_id);
CREATE INDEX tickets_assignee_id_idx ON tickets (assignee_id);

CREATE TABLE ticket_messages (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ticket_id bigint NOT NULL,
    agent_id uuid,
    customer_id uuid,
    body text NOT NULL,
    internal boolean DEFAULT false NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL,
    CHECK (agent_id is not null or customer_id is not null)
);
CREATE INDEX ticket_messages_ticket_idx ON ticket_messages (ticket_id, created_at);
CREATE INDEX ticket_messages_agent_id_idx ON ticket_messages (agent_id);
CREATE INDEX ticket_messages_customer_id_idx ON ticket_messages (customer_id);

CREATE TABLE tags (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE ticket_tags (
    ticket_id bigint NOT NULL,
    tag_id uuid NOT NULL,
    PRIMARY KEY (ticket_id, tag_id)
);
CREATE INDEX ticket_tags_tag_id_idx ON ticket_tags (tag_id);

CREATE TABLE sla_policies (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    priority ticket_priority NOT NULL UNIQUE,
    first_response_minutes int NOT NULL,
    resolution_minutes int NOT NULL
);

ALTER TABLE tickets ADD FOREIGN KEY (customer_id) REFERENCES customers (id);

ALTER TABLE tickets ADD FOREIGN KEY (assignee_id) REFERENCES agents (id) ON DELETE SET NULL;

ALTER TABLE ticket_messages ADD FOREIGN KEY (ticket_id) REFERENCES tickets (id) ON DELETE CASCADE;

ALTER TABLE ticket_messages ADD FOREIGN KEY (agent_id) REFERENCES agents (id) ON DELETE SET NULL;

ALTER TABLE ticket_messages ADD FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE SET NULL;

ALTER TABLE ticket_tags ADD FOREIGN KEY (ticket_id) REFERENCES tickets (id) ON DELETE CASCADE;

ALTER TABLE ticket_tags ADD FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE;