BEGIN;
CREATE TABLE IF NOT EXISTS it_members (
 user_id BIGINT PRIMARY KEY REFERENCES users(id),
 role VARCHAR(16) NOT NULL DEFAULT 'member' CHECK(role IN ('member','technician','manager')),
 added_by BIGINT NOT NULL REFERENCES users(id), created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE IF NOT EXISTS it_trackers (id BIGSERIAL PRIMARY KEY,name VARCHAR(80) NOT NULL UNIQUE);
INSERT INTO it_trackers(name) VALUES ('Incident'),('Service Request'),('Hardware'),('Software'),('Network'),('Access Request'),('Maintenance') ON CONFLICT(name) DO NOTHING;
CREATE TABLE IF NOT EXISTS it_tickets (
 id BIGSERIAL PRIMARY KEY,subject VARCHAR(200) NOT NULL,description TEXT NOT NULL,
 campus VARCHAR(180) NOT NULL,tracker_id BIGINT NOT NULL REFERENCES it_trackers(id),
 priority VARCHAR(12) NOT NULL CHECK(priority IN ('low','normal','high','urgent')),
 status VARCHAR(16) NOT NULL DEFAULT 'new' CHECK(status IN ('new','assigned','in_progress','waiting','resolved','closed')),
 percent INTEGER NOT NULL DEFAULT 0 CHECK(percent BETWEEN 0 AND 100 AND percent % 10=0),
 reporter_id BIGINT NOT NULL REFERENCES users(id),assignee_id BIGINT REFERENCES users(id),assigner_id BIGINT REFERENCES users(id),
 due_date DATE,resolution TEXT NOT NULL DEFAULT '',version INTEGER NOT NULL DEFAULT 1,
 created_at TIMESTAMPTZ NOT NULL DEFAULT now(),updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),closed_at TIMESTAMPTZ,
 CHECK(status NOT IN ('resolved','closed') OR percent=100),
 CHECK(status<>'closed' OR length(trim(resolution))>0)
);
CREATE INDEX IF NOT EXISTS it_tickets_queue ON it_tickets(status,updated_at DESC,id DESC);
CREATE INDEX IF NOT EXISTS it_tickets_assignee ON it_tickets(assignee_id,updated_at DESC);
CREATE INDEX IF NOT EXISTS it_tickets_reporter ON it_tickets(reporter_id,updated_at DESC);
CREATE TABLE IF NOT EXISTS it_watchers (
 ticket_id BIGINT NOT NULL REFERENCES it_tickets(id),user_id BIGINT NOT NULL REFERENCES users(id),
 added_by BIGINT NOT NULL REFERENCES users(id),PRIMARY KEY(ticket_id,user_id)
);
CREATE INDEX IF NOT EXISTS it_watchers_user ON it_watchers(user_id,ticket_id);
CREATE TABLE IF NOT EXISTS it_messages (
 id BIGSERIAL PRIMARY KEY,ticket_id BIGINT NOT NULL REFERENCES it_tickets(id),author_id BIGINT NOT NULL REFERENCES users(id),
 recipient_id BIGINT NOT NULL REFERENCES users(id),body TEXT NOT NULL,created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS it_messages_ticket ON it_messages(ticket_id,id DESC);
CREATE TABLE IF NOT EXISTS it_events (
 id BIGSERIAL PRIMARY KEY,ticket_id BIGINT NOT NULL REFERENCES it_tickets(id),actor_id BIGINT NOT NULL REFERENCES users(id),
 action VARCHAR(40) NOT NULL,details JSONB NOT NULL DEFAULT '{}',created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS it_events_ticket ON it_events(ticket_id,id DESC);
CREATE TABLE IF NOT EXISTS it_attachments (
 id BIGSERIAL PRIMARY KEY,ticket_id BIGINT NOT NULL REFERENCES it_tickets(id),uploader_id BIGINT NOT NULL REFERENCES users(id),
 filename VARCHAR(200) NOT NULL,mime VARCHAR(150) NOT NULL,size_bytes INTEGER NOT NULL CHECK(size_bytes BETWEEN 1 AND 5242880),
 sha256 CHAR(64) NOT NULL,storage_key CHAR(64) NOT NULL UNIQUE,created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS it_attachments_ticket ON it_attachments(ticket_id,id);
CREATE TABLE IF NOT EXISTS it_notifications (
 id BIGSERIAL PRIMARY KEY,user_id BIGINT NOT NULL REFERENCES users(id),ticket_id BIGINT NOT NULL REFERENCES it_tickets(id),
 message VARCHAR(250) NOT NULL,is_read BOOLEAN NOT NULL DEFAULT false,created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS it_notifications_user ON it_notifications(user_id,is_read,id DESC);
-- A mutation and its receipt commit together: retries never duplicate a ticket/message/file.
CREATE TABLE IF NOT EXISTS it_receipts (
 user_id BIGINT NOT NULL REFERENCES users(id),client_key CHAR(32) NOT NULL,request_hash CHAR(64) NOT NULL,
 result JSONB NOT NULL,created_at TIMESTAMPTZ NOT NULL DEFAULT now(),PRIMARY KEY(user_id,client_key)
);
COMMIT;
