1-- Create a table for user blacklists
2CREATE TABLE IF NOT EXISTS user_blacklists (
3 user_id TEXT PRIMARY KEY,
4 blacklisted_tags TEXT[] DEFAULT ARRAY[]::TEXT[],
5 created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
6 updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
7);
8
9-- Create a table for favorite tags
10CREATE TABLE IF NOT EXISTS user_favorites (
11 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
12 user_id TEXT NOT NULL,
13 tag TEXT NOT NULL,
14 created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
15 UNIQUE(user_id, tag)
16);
17
18-- Create a table for booru history
19CREATE TABLE IF NOT EXISTS booru_history (
20 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
21 user_id TEXT NOT NULL,
22 site TEXT NOT NULL,
23 tags TEXT[],
24 post_url TEXT NOT NULL,
25 created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
26);
27
28-- Create a table for global blacklisted tags
29CREATE TABLE IF NOT EXISTS global_blacklist (
30 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
31 tag TEXT NOT NULL UNIQUE,
32 created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
33);
34
35-- Insert some default blacklisted tags
36INSERT INTO global_blacklist (tag) VALUES
37 ('gore'),
38 ('scat'),
39 ('young'),
40 ('loli'),
41 ('shota');
42
43-- Create function to update updated_at timestamp
44CREATE OR REPLACE FUNCTION update_updated_at_column()
45RETURNS TRIGGER AS $$
46BEGIN
47 NEW.updated_at = NOW();
48 RETURN NEW;
49END;
50$$ language 'plpgsql';
51
52-- Create trigger for user_blacklists
53DROP TRIGGER IF EXISTS update_user_blacklists_updated_at ON user_blacklists;
54CREATE TRIGGER update_user_blacklists_updated_at
55 BEFORE UPDATE ON user_blacklists
56 FOR EACH ROW
57 EXECUTE FUNCTION update_updated_at_column();
58
59-- Create a table for blacklist suggestions
60CREATE TABLE IF NOT EXISTS blacklist_suggestions (
61 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
62 tag TEXT NOT NULL,
63 reason TEXT,
64 suggested_by TEXT,
65 status TEXT NOT NULL DEFAULT 'pending',
66 created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
67 updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL,
68 UNIQUE(tag, status)
69);
70
71-- Create trigger for blacklist_suggestions
72DROP TRIGGER IF EXISTS update_blacklist_suggestions_updated_at ON blacklist_suggestions;
73CREATE TRIGGER update_blacklist_suggestions_updated_at
74 BEFORE UPDATE ON blacklist_suggestions
75 FOR EACH ROW
76 EXECUTE FUNCTION update_updated_at_column();
77
78-- Create a table for reminders
79CREATE TABLE IF NOT EXISTS reminders (
80 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
81 user_id TEXT NOT NULL,
82 channel_id TEXT NOT NULL,
83 message TEXT NOT NULL,
84 remind_at TIMESTAMP WITH TIME ZONE NOT NULL,
85 created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
86);
87
88-- Create a table for user levels
89CREATE TABLE IF NOT EXISTS user_levels (
90 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
91 user_id TEXT NOT NULL,
92 xp INTEGER DEFAULT 0,
93 level INTEGER DEFAULT 1,
94 last_message_at TIMESTAMP WITH TIME ZONE,
95 UNIQUE(user_id)
96);
97
98-- Add giveaway table
99CREATE TABLE IF NOT EXISTS giveaways (
100 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
101 message_id TEXT NOT NULL UNIQUE,
102 channel_id TEXT NOT NULL,
103 prize TEXT NOT NULL,
104 winner_count INTEGER DEFAULT 1,
105 host_id TEXT NOT NULL,
106 ends_at TIMESTAMP WITH TIME ZONE NOT NULL,
107 ended BOOLEAN DEFAULT FALSE,
108 created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
109);
110