DatabaseCreation_PostgreSQL.sql

-- ============================================================
-- TexitArchenemy - PostgreSQL Schema
-- Converted from T-SQL / MSSQL
-- ============================================================

-- Run as superuser or a role with CREATEDB, then switch to the new DB.
-- Example:
--   createdb texit_archenemy
--   psql -d texit_archenemy -f DatabaseCreation_PostgreSQL.sql


-- ============================================================
-- TABLES
-- ============================================================

CREATE TABLE discord_auth (
    token VARCHAR(60) NOT NULL
);

CREATE TABLE twitter_auth (
    api_key    VARCHAR(25)  NOT NULL,
    api_secret VARCHAR(50)  NOT NULL,
    api_token  VARCHAR(116) NOT NULL
);

-- GENERATED BY DEFAULT (not ALWAYS) lets us INSERT explicit IDs during data migration.
-- After migrating data, reset the sequence (see migration guide).
CREATE TABLE channel_types (
    channel_type_id          INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    channel_type_description VARCHAR(10) NOT NULL
);
INSERT INTO channel_types (channel_type_description) VALUES ('text'), ('voice');

-- BIT -> BOOLEAN, DEFAULT 0 -> DEFAULT FALSE
CREATE TABLE discord_channels (
    channel_id      VARCHAR(20) PRIMARY KEY,
    guild_id        VARCHAR(20) NOT NULL,
    channel_type_id INT         NOT NULL DEFAULT 1,
    repost_check    BOOLEAN     NOT NULL DEFAULT FALSE,
    pixiv_expand    BOOLEAN     NOT NULL DEFAULT FALSE,
    FOREIGN KEY (channel_type_id) REFERENCES channel_types(channel_type_id)
);

CREATE TABLE twitter_rules (
    tag        INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    rule_value VARCHAR(512) UNIQUE NOT NULL
);

CREATE TABLE rule_channel_relation (
    tag        INT         NOT NULL,
    channel_id VARCHAR(20) NOT NULL,
    PRIMARY KEY (tag, channel_id),
    FOREIGN KEY (tag)        REFERENCES twitter_rules(tag)       ON DELETE CASCADE,
    FOREIGN KEY (channel_id) REFERENCES discord_channels(channel_id) ON DELETE CASCADE
);

CREATE TABLE link_types (
    link_type_id          INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    link_type_description VARCHAR(10) NOT NULL UNIQUE
);
INSERT INTO link_types (link_type_description) VALUES ('Twitter'), ('Pixiv'), ('Artstation');

CREATE TABLE repost_repository (
    message_id   VARCHAR(20) NOT NULL,
    channel_id   VARCHAR(20) NOT NULL,
    link_id      VARCHAR(20) NOT NULL,
    link_type_id INT         NOT NULL,
    PRIMARY KEY (message_id, channel_id, link_id, link_type_id),
    FOREIGN KEY (link_type_id) REFERENCES link_types(link_type_id),
    FOREIGN KEY (channel_id)   REFERENCES discord_channels(channel_id) ON DELETE CASCADE
);

CREATE TABLE draw_a_box_warmups (
    warmup VARCHAR(50) NOT NULL,
    lesson INT         NOT NULL
);
INSERT INTO draw_a_box_warmups (warmup, lesson) VALUES
    ('Superimposed Lines',              1),
    ('Table of Ellipses',               1),
    ('Ellipses in Planes',              1),
    ('Funnels',                         1),
    ('Plotted Perspective',             1),
    ('Rough Perspective',               1),
    ('Rotated Boxes',                   1),
    ('Organic Perspective',             1),
    ('Organic Arrows',                  2),
    ('Organic Forms with Contour Lines',2),
    ('Texture Analysis',                2),
    ('Dissections',                     2),
    ('Form Intersections',              2),
    ('Organic Intersections',           2);

CREATE TABLE draw_a_box_box_challenge (
    user_id     VARCHAR(20) PRIMARY KEY,
    boxes_drawn INT NOT NULL
);


-- ============================================================
-- FUNCTIONS  (replacing T-SQL stored procedures)
--
-- All data-returning procedures become FUNCTIONS (RETURNS TABLE).
-- They are called from C# as: SELECT * FROM func_name(@p1, @p2)
-- ============================================================

-- ---- Credentials ----

CREATE OR REPLACE FUNCTION get_discord_creds()
RETURNS TABLE(token VARCHAR(60))
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY SELECT d.token FROM discord_auth d LIMIT 1;
END;
$$;

CREATE OR REPLACE FUNCTION get_twitter_creds()
RETURNS TABLE(api_key VARCHAR(25), api_secret VARCHAR(50), api_token VARCHAR(116))
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY SELECT t.api_key, t.api_secret, t.api_token FROM twitter_auth t LIMIT 1;
END;
$$;

-- ---- Repost checking ----

-- Returns (channel_id, message_id) of existing repost, or ('-1', '-1') if not a repost (and inserts it).
CREATE OR REPLACE FUNCTION check_repost(
    p_channel_id          VARCHAR(20),
    p_message_id          VARCHAR(20),
    p_link_id             VARCHAR(20),
    p_link_type_description VARCHAR(20)
)
RETURNS TABLE(channel_id VARCHAR(20), message_id VARCHAR(20))
LANGUAGE plpgsql AS $$
DECLARE
    v_count INT;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM repost_repository r
    INNER JOIN link_types lt ON r.link_type_id = lt.link_type_id
    WHERE r.channel_id              = p_channel_id
      AND r.link_id                 = p_link_id
      AND lt.link_type_description  = p_link_type_description;

    IF v_count > 0 THEN
        RETURN QUERY
            SELECT r.channel_id, r.message_id
            FROM repost_repository r
            INNER JOIN link_types lt ON r.link_type_id = lt.link_type_id
            WHERE r.channel_id              = p_channel_id
              AND r.link_id                 = p_link_id
              AND lt.link_type_description  = p_link_type_description;
    ELSE
        INSERT INTO repost_repository (channel_id, message_id, link_id, link_type_id)
        VALUES (
            p_channel_id,
            p_message_id,
            p_link_id,
            (SELECT lt.link_type_id FROM link_types lt WHERE lt.link_type_description = p_link_type_description)
        );
        RETURN QUERY SELECT '-1'::VARCHAR(20), '-1'::VARCHAR(20);
    END IF;
END;
$$;

CREATE OR REPLACE FUNCTION preemptive_repost_check(
    p_channel_id            VARCHAR(20),
    p_link_id               VARCHAR(20),
    p_link_type_description VARCHAR(20)
)
RETURNS TABLE(repost_number BIGINT)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
        SELECT COUNT(*)
        FROM repost_repository r
        INNER JOIN link_types lt ON r.link_type_id = lt.link_type_id
        WHERE r.channel_id              = p_channel_id
          AND r.link_id                 = p_link_id
          AND lt.link_type_description  = p_link_type_description;
END;
$$;

CREATE OR REPLACE FUNCTION is_repost_channel(p_channel_id VARCHAR(20))
RETURNS TABLE(repost_check BOOLEAN)
LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM discord_channels dc WHERE dc.channel_id = p_channel_id) THEN
        RETURN QUERY SELECT FALSE;
    ELSE
        RETURN QUERY
            SELECT dc.repost_check
            FROM discord_channels dc
            WHERE dc.channel_id = p_channel_id;
    END IF;
END;
$$;

-- ---- Twitter rules ----

CREATE OR REPLACE FUNCTION get_twitter_rules()
RETURNS TABLE(tag INT, rule_value VARCHAR(512))
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY SELECT tr.tag, tr.rule_value FROM twitter_rules tr;
END;
$$;

CREATE OR REPLACE FUNCTION get_twitter_rule_channels(p_tag INT)
RETURNS TABLE(channel_id VARCHAR(20))
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
        SELECT rcr.channel_id
        FROM rule_channel_relation rcr
        WHERE rcr.tag = p_tag;
END;
$$;

-- Returns (tag, added): tag=-1 and added=false means the channel<->rule pair already existed.
CREATE OR REPLACE FUNCTION add_twitter_rule(
    p_channel_id VARCHAR(20),
    p_guild_id   VARCHAR(20),
    p_rule_value VARCHAR(512)
)
RETURNS TABLE(tag INT, added BOOLEAN)
LANGUAGE plpgsql AS $$
DECLARE
    v_tag           INT;
    v_actually_added BOOLEAN := FALSE;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM discord_channels dc WHERE dc.channel_id = p_channel_id) THEN
        INSERT INTO discord_channels (channel_id, guild_id) VALUES (p_channel_id, p_guild_id);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM twitter_rules tr WHERE tr.rule_value = p_rule_value) THEN
        INSERT INTO twitter_rules (rule_value) VALUES (p_rule_value);
        v_actually_added := TRUE;
    END IF;

    SELECT tr.tag INTO v_tag FROM twitter_rules tr WHERE tr.rule_value = p_rule_value;

    BEGIN
        INSERT INTO rule_channel_relation (tag, channel_id) VALUES (v_tag, p_channel_id);
        RETURN QUERY SELECT v_tag, v_actually_added;
    EXCEPTION WHEN unique_violation OR foreign_key_violation THEN
        RETURN QUERY SELECT -1::INT, FALSE::BOOLEAN;
    END;
END;
$$;

-- ---- Draw-a-Box ----

CREATE OR REPLACE FUNCTION get_box_warmup(p_lesson INT)
RETURNS TABLE(warmup VARCHAR(50))
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
        SELECT w.warmup
        FROM draw_a_box_warmups w
        WHERE w.lesson <= p_lesson;
END;
$$;

-- T-SQL used a VALUES table trick for GREATEST(x, 0). PostgreSQL has GREATEST() natively.
CREATE OR REPLACE FUNCTION update_box_challenge_progress(
    p_user_id    VARCHAR(20),
    p_boxes_drawn INT
)
RETURNS TABLE(boxes_drawn INT)
LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM draw_a_box_box_challenge dbc WHERE dbc.user_id = p_user_id) THEN
        INSERT INTO draw_a_box_box_challenge (user_id, boxes_drawn)
        VALUES (p_user_id, GREATEST(p_boxes_drawn, 0));
    ELSE
        UPDATE draw_a_box_box_challenge dbc
        SET boxes_drawn = GREATEST(dbc.boxes_drawn + p_boxes_drawn, 0)
        WHERE dbc.user_id = p_user_id;
    END IF;

    RETURN QUERY
        SELECT dbc.boxes_drawn
        FROM draw_a_box_box_challenge dbc
        WHERE dbc.user_id = p_user_id;
END;
$$;

CREATE OR REPLACE FUNCTION get_box_challenge_progress(p_user_id VARCHAR(20))
RETURNS TABLE(boxes_drawn INT)
LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM draw_a_box_box_challenge dbc WHERE dbc.user_id = p_user_id) THEN
        RETURN QUERY SELECT 0::INT;
    ELSE
        RETURN QUERY
            SELECT dbc.boxes_drawn
            FROM draw_a_box_box_challenge dbc
            WHERE dbc.user_id = p_user_id;
    END IF;
END;
$$;


-- ============================================================
-- PROCEDURES  (void operations — no result set returned)
-- Called from C# as: CALL proc_name(@p1, @p2)
-- ============================================================

CREATE OR REPLACE PROCEDURE mark_as_repost_channel(
    p_channel_id VARCHAR(20),
    p_guild_id   VARCHAR(20)
)
LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM discord_channels dc WHERE dc.channel_id = p_channel_id) THEN
        INSERT INTO discord_channels (channel_id, channel_type_id, guild_id, pixiv_expand, repost_check)
        VALUES (p_channel_id, 1, p_guild_id, FALSE, TRUE);
    ELSE
        UPDATE discord_channels
        SET repost_check = TRUE
        WHERE channel_id = p_channel_id;
    END IF;
END;
$$;

CREATE OR REPLACE PROCEDURE delete_twitter_rule(p_tag INT, p_channel_id VARCHAR(20))
LANGUAGE plpgsql AS $$
BEGIN
    DELETE FROM rule_channel_relation
    WHERE tag = p_tag AND channel_id = p_channel_id;
END;
$$;

-- Internal helper called by trigger functions below
CREATE OR REPLACE PROCEDURE remove_useless_channels()
LANGUAGE plpgsql AS $$
BEGIN
    DELETE FROM discord_channels
    WHERE pixiv_expand  = FALSE
      AND repost_check  = FALSE
      AND channel_id NOT IN (SELECT rcr.channel_id FROM rule_channel_relation rcr);
END;
$$;


-- ============================================================
-- TRIGGERS
--
-- PostgreSQL triggers require a separate trigger function
-- (RETURNS TRIGGER) plus a CREATE TRIGGER statement.
-- ============================================================

-- Cleans up stored reposts when repost_check is turned off for a channel.
CREATE OR REPLACE FUNCTION fn_repost_cleanup() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    -- Only act when repost_check transitions from TRUE to FALSE
    IF OLD.repost_check = TRUE AND NEW.repost_check = FALSE THEN
        DELETE FROM repost_repository WHERE channel_id = NEW.channel_id;
    END IF;
    RETURN NULL;
END;
$$;

CREATE TRIGGER repost_cleanup
AFTER UPDATE ON discord_channels
FOR EACH ROW EXECUTE FUNCTION fn_repost_cleanup();


-- Removes channels that are no longer useful after an update.
CREATE OR REPLACE FUNCTION fn_channel_cleanup_after_update() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    CALL remove_useless_channels();
    RETURN NULL;
END;
$$;

CREATE TRIGGER channel_cleanup
AFTER UPDATE ON discord_channels
FOR EACH ROW EXECUTE FUNCTION fn_channel_cleanup_after_update();


-- Removes orphan twitter_rules (rules with no channel associations) after a rule-channel link is deleted.
CREATE OR REPLACE FUNCTION fn_rule_cleanup() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    DELETE FROM twitter_rules
    WHERE tag NOT IN (SELECT rcr.tag FROM rule_channel_relation rcr);
    RETURN NULL;
END;
$$;

CREATE TRIGGER rule_cleanup
AFTER DELETE ON rule_channel_relation
FOR EACH ROW EXECUTE FUNCTION fn_rule_cleanup();


-- Removes channels made useless after a rule-channel link is deleted.
CREATE OR REPLACE FUNCTION fn_channel_cleanup_after_relation_delete() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    CALL remove_useless_channels();
    RETURN NULL;
END;
$$;

CREATE TRIGGER channel_cleanup_relation
AFTER DELETE ON rule_channel_relation
FOR EACH ROW EXECUTE FUNCTION fn_channel_cleanup_after_relation_delete();