-- +brant Up

-- Users. spec.sr.ht is single-owner, but the webhook subscription is
-- user-scoped in the core-go convention, so a user row is the owner's identity
-- and the FK target. Columns mirror what core-go's auth.LookupUser selects, so
-- the table is ready if the service ever adopts core-go auth wholesale; today
-- only id/username are used (the owner row is seeded at startup).
CREATE TYPE user_type AS ENUM ('PENDING','USER','ADMIN','SUSPENDED');
CREATE TABLE "user" (
	id                serial PRIMARY KEY,
	created           timestamp NOT NULL,
	updated           timestamp NOT NULL,
	username          varchar(256) NOT NULL UNIQUE,
	email             varchar(256) NOT NULL DEFAULT '',
	user_type         user_type NOT NULL DEFAULT 'USER',
	url               varchar,
	location          varchar,
	bio               varchar,
	suspension_notice varchar
);

-- Proposal lifecycle events a webhook may subscribe to.
CREATE TYPE webhook_event AS ENUM ('PROPOSAL_OPENED','PROPOSAL_MERGED','PROPOSAL_REJECTED');

-- The auth captured on a subscription at creation time (core-go webhooks/config.go
-- AuthConfig). Only OAUTH2 and INTERNAL are storable; the check enforces it.
CREATE TYPE auth_method AS ENUM ('OAUTH_LEGACY','OAUTH2','COOKIE','INTERNAL','WEBHOOK');

-- GraphQL-native user webhook subscription. The auth columns
-- (auth_method/token_hash/grants/client_id/expires/node_id) mirror core-go's
-- webhooks.WebhookSubscription exactly; the engine selects them by these names.
CREATE TABLE gql_user_wh_sub (
	id          serial PRIMARY KEY,
	created     timestamp NOT NULL,
	events      webhook_event[] NOT NULL CHECK (cardinality(events) > 0),
	url         varchar NOT NULL,
	query       varchar NOT NULL,
	auth_method auth_method NOT NULL CHECK (auth_method IN ('OAUTH2','INTERNAL')),
	token_hash  varchar(128) CHECK ((auth_method = 'OAUTH2') = (token_hash IS NOT NULL)),
	grants      varchar,
	client_id   uuid,
	expires     timestamp CHECK ((auth_method = 'OAUTH2') = (expires IS NOT NULL)),
	node_id     varchar CHECK ((auth_method = 'INTERNAL') = (node_id IS NOT NULL)),
	user_id     integer NOT NULL REFERENCES "user"(id) ON DELETE CASCADE
);
CREATE INDEX gql_user_wh_sub_token_hash_idx ON gql_user_wh_sub (token_hash);

-- Delivery records. Columns match core-go's insert/update in webhooks/queue.go.
CREATE TABLE gql_user_wh_delivery (
	id               serial PRIMARY KEY,
	uuid             uuid NOT NULL,
	date             timestamp NOT NULL,
	event            webhook_event NOT NULL,
	subscription_id  integer NOT NULL REFERENCES gql_user_wh_sub(id) ON DELETE CASCADE,
	request_body     varchar NOT NULL,
	response_body    varchar,
	response_headers varchar,
	response_status  integer
);

-- +brant Down
DROP TABLE gql_user_wh_delivery;
DROP TABLE gql_user_wh_sub;
DROP TYPE auth_method;
DROP TYPE webhook_event;
DROP TABLE "user";
DROP TYPE user_type;