CREATE TABLE IF NOT EXISTS "server"
(
    "id"       UUID    NOT NULL PRIMARY KEY DEFAULT uuid_generate_v7(),
    "owner_id" UUID    NOT NULL REFERENCES "user" ("id"),
    "name"     VARCHAR NOT NULL,
    "icon_id"  UUID    REFERENCES "file" ("id") ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS "server_role"
(
    "id"          UUID     NOT NULL PRIMARY KEY DEFAULT uuid_generate_v7(),
    "server_id"   UUID     NOT NULL REFERENCES "server" ("id") ON DELETE CASCADE,
    "name"        VARCHAR  NOT NULL,
    "color"       VARCHAR,
    "display"     BOOLEAN  NOT NULL             DEFAULT FALSE,
    "permissions" JSONB    NOT NULL             DEFAULT '{}'::JSONB,
    "position"    SMALLINT NOT NULL
);

CREATE TABLE IF NOT EXISTS "server_member"
(
    "id"         UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v7(),
    "server_id"  UUID NOT NULL REFERENCES "server" ("id") ON DELETE CASCADE,
    "user_id"    UUID NOT NULL REFERENCES "user" ("id") ON DELETE CASCADE,
    "nickname"   VARCHAR,
    "avatar_url" VARCHAR,
    UNIQUE ("server_id", "user_id")
);

CREATE TABLE IF NOT EXISTS "server_member_role"
(
    "member_id" UUID NOT NULL REFERENCES "server_member" ("id") ON DELETE CASCADE,
    "role_id"   UUID NOT NULL REFERENCES "server_role" ("id") ON DELETE CASCADE,
    PRIMARY KEY ("member_id", "role_id")
);

CREATE TABLE IF NOT EXISTS "server_invite"
(
    "code"       VARCHAR NOT NULL PRIMARY KEY,
    "server_id"  UUID    NOT NULL REFERENCES "server" ("id") ON DELETE CASCADE,
    "inviter_id" UUID    REFERENCES "user" ("id") ON DELETE SET NULL,
    "expires_at" TIMESTAMPTZ
);

CREATE OR REPLACE FUNCTION check_icon_is_image()
    RETURNS TRIGGER AS
$$
DECLARE
    file_content_type VARCHAR;
BEGIN
    -- Skip check if icon_id is null
    IF NEW.icon_id IS NULL THEN
        RETURN NEW;
    END IF;

    -- Retrieve content_type from file table
    SELECT content_type
    INTO file_content_type
    FROM file
    WHERE id = NEW.icon_id;

    -- Raise exception if content_type does not start with 'image/'
    IF file_content_type IS NULL OR file_content_type NOT LIKE 'image/%' THEN
        RAISE EXCEPTION 'icon_id must reference a file with content_type starting with image/';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_icon_is_image
    BEFORE INSERT OR UPDATE
    ON "server"
    FOR EACH ROW
EXECUTE FUNCTION check_icon_is_image();

CREATE OR REPLACE FUNCTION check_server_user_role_server_id()
    RETURNS TRIGGER AS
$$
DECLARE
    member_server_id UUID;
    role_server_id   UUID;
BEGIN
    -- Get server_id from server_user
    SELECT server_id
    INTO member_server_id
    FROM server_member
    WHERE id = NEW.member_id;

    -- Get server_id from server_role
    SELECT server_id
    INTO role_server_id
    FROM server_role
    WHERE id = NEW.role_id;

    -- Check if server_ids match
    IF member_server_id != role_server_id THEN
        RAISE EXCEPTION 'Cannot assign role from a different server: server_user server_id (%) does not match server_role server_id (%)', member_server_id, role_server_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_server_user_role_server_id
    BEFORE INSERT OR UPDATE
    ON server_member_role
    FOR EACH ROW
EXECUTE FUNCTION check_server_user_role_server_id();