CREATE TABLE IF NOT EXISTS "user"
(
    "id"            UUID    NOT NULL PRIMARY KEY DEFAULT uuid_generate_v7(),
    "avatar_id"     UUID    REFERENCES "file" ("id") ON DELETE SET NULL,
    "username"      VARCHAR NOT NULL UNIQUE,
    "display_name"  VARCHAR,
    "email"         VARCHAR NOT NULL,
    "password_hash" VARCHAR NOT NULL,
    "bot"           BOOLEAN NOT NULL             DEFAULT FALSE,
    "system"        BOOLEAN NOT NULL             DEFAULT FALSE,
    "settings"      JSONB   NOT NULL             DEFAULT '{}'::JSONB
);

CREATE TABLE IF NOT EXISTS "user_relation"
(
    "user_id"    UUID        NOT NULL CHECK (user_id <> other_id) REFERENCES "user" ("id") ON DELETE CASCADE,
    "other_id"   UUID        NOT NULL CHECK (user_id <> other_id) REFERENCES "user" ("id") ON DELETE CASCADE,
    "type"       INT2        NOT NULL,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
    "updated_at" TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY ("user_id", "other_id")
);

-- create system account
INSERT INTO "user" ("username", "display_name", "email", "password_hash", "bot", "system")
VALUES ('system', 'System', 'system@lionarius.ru', '', TRUE, TRUE);

CREATE OR REPLACE FUNCTION check_avatar_is_image()
    RETURNS TRIGGER AS
$$
DECLARE
    file_content_type VARCHAR;
BEGIN
    -- Skip check if icon_id is null
    IF NEW.avatar_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.avatar_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 'avatar_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 "user"
    FOR EACH ROW
EXECUTE FUNCTION check_avatar_is_image();

CREATE OR REPLACE FUNCTION fn_on_user_relation_update()
    RETURNS TRIGGER
    LANGUAGE plpgsql
AS
$$
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        NEW.updated_at := now();
    END IF;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_user_relation_update
    BEFORE UPDATE
    ON "user_relation"
    FOR EACH ROW
EXECUTE FUNCTION fn_on_user_relation_update();