204 lines
7.5 KiB
Go
204 lines
7.5 KiB
Go
package db
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
|
|
_ "modernc.org/sqlite"
|
|
)
|
|
|
|
func Open(path string) (*sql.DB, error) {
|
|
db, err := sql.Open("sqlite", path+"?_journal=WAL&_timeout=5000&_fk=true")
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
db.SetMaxOpenConns(1) // SQLite is single-writer
|
|
if err := migrate(db); err != nil {
|
|
db.Close()
|
|
return nil, fmt.Errorf("migrate: %w", err)
|
|
}
|
|
return db, nil
|
|
}
|
|
|
|
func migrate(db *sql.DB) error {
|
|
ctx := context.Background()
|
|
if _, err := db.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL)`); err != nil {
|
|
return err
|
|
}
|
|
var version int
|
|
db.QueryRowContext(ctx, `SELECT COALESCE(MAX(version),0) FROM schema_version`).Scan(&version) //nolint
|
|
|
|
migrations := []string{
|
|
// v1: core schema
|
|
`CREATE TABLE IF NOT EXISTS tickets (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
ticket_number INTEGER NOT NULL,
|
|
user_id TEXT NOT NULL,
|
|
panel TEXT NOT NULL,
|
|
type TEXT NOT NULL,
|
|
channel_id TEXT NOT NULL UNIQUE,
|
|
opened_at DATETIME NOT NULL,
|
|
claimed_by TEXT,
|
|
claimed_at DATETIME,
|
|
closed_at DATETIME,
|
|
closed_by TEXT,
|
|
reason TEXT,
|
|
transcript_path TEXT,
|
|
status TEXT NOT NULL DEFAULT 'open'
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_tickets_user ON tickets(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_tickets_status ON tickets(status);
|
|
CREATE INDEX IF NOT EXISTS idx_tickets_type ON tickets(type);
|
|
CREATE TABLE IF NOT EXISTS claim_messages (
|
|
ticket_id INTEGER PRIMARY KEY,
|
|
message_id TEXT NOT NULL,
|
|
last_reup_at DATETIME NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS convocation_counter (
|
|
id INTEGER PRIMARY KEY CHECK(id=1),
|
|
count INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
INSERT OR IGNORE INTO convocation_counter(id,count) VALUES(1,0);`,
|
|
// v2: user-supplied ticket title and description (from modal)
|
|
`ALTER TABLE tickets ADD COLUMN ticket_title TEXT;
|
|
ALTER TABLE tickets ADD COLUMN ticket_description TEXT;`,
|
|
// v3: panel web admin tables
|
|
`CREATE TABLE IF NOT EXISTS panel_admins (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
discord_id TEXT NOT NULL UNIQUE,
|
|
discord_username TEXT NOT NULL DEFAULT '',
|
|
discord_avatar TEXT NOT NULL DEFAULT '',
|
|
password_hash TEXT NOT NULL,
|
|
totp_secret TEXT,
|
|
totp_enabled INTEGER NOT NULL DEFAULT 0,
|
|
is_superadmin INTEGER NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS panel_sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
token TEXT NOT NULL UNIQUE,
|
|
csrf_token TEXT NOT NULL,
|
|
admin_id INTEGER NOT NULL REFERENCES panel_admins(id) ON DELETE CASCADE,
|
|
ip_address TEXT NOT NULL DEFAULT '',
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
last_activity DATETIME NOT NULL,
|
|
created_at DATETIME NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_token ON panel_sessions(token);
|
|
CREATE TABLE IF NOT EXISTS panel_configs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
embed_title TEXT NOT NULL DEFAULT '',
|
|
embed_description TEXT,
|
|
embed_color TEXT NOT NULL DEFAULT '#5865f2',
|
|
guild_id TEXT NOT NULL DEFAULT '',
|
|
channel_id TEXT,
|
|
message_id TEXT,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS panel_types (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
panel_id INTEGER NOT NULL REFERENCES panel_configs(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
button_label TEXT NOT NULL DEFAULT '',
|
|
button_color TEXT NOT NULL DEFAULT 'primary',
|
|
button_emoji TEXT NOT NULL DEFAULT '',
|
|
embed_color TEXT NOT NULL DEFAULT '#5865f2',
|
|
embed_title TEXT NOT NULL DEFAULT '',
|
|
embed_text TEXT NOT NULL DEFAULT '',
|
|
staff_role_id TEXT NOT NULL DEFAULT '',
|
|
category_id TEXT NOT NULL DEFAULT '',
|
|
claim_channel_id TEXT NOT NULL DEFAULT '',
|
|
log_channel_id TEXT NOT NULL DEFAULT '',
|
|
max_per_user INTEGER NOT NULL DEFAULT 1,
|
|
claim_mode INTEGER NOT NULL DEFAULT 1,
|
|
close_rule TEXT NOT NULL DEFAULT 'staff_only',
|
|
modal_enabled INTEGER NOT NULL DEFAULT 1,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL,
|
|
UNIQUE(panel_id, name)
|
|
);
|
|
CREATE TABLE IF NOT EXISTS convocation_config (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
guild_id TEXT NOT NULL UNIQUE,
|
|
category_id TEXT NOT NULL DEFAULT '',
|
|
log_channel_id TEXT NOT NULL DEFAULT '',
|
|
modal_enabled INTEGER NOT NULL DEFAULT 1,
|
|
updated_at DATETIME NOT NULL
|
|
);
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
admin_id INTEGER NOT NULL REFERENCES panel_admins(id),
|
|
action TEXT NOT NULL,
|
|
entity_type TEXT NOT NULL,
|
|
entity_id INTEGER,
|
|
old_value TEXT,
|
|
new_value TEXT,
|
|
ip_address TEXT NOT NULL DEFAULT '',
|
|
created_at DATETIME NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_admin ON audit_log(admin_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_entity ON audit_log(entity_type, entity_id);
|
|
CREATE TABLE IF NOT EXISTS user_blacklist (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
discord_id TEXT NOT NULL UNIQUE,
|
|
reason TEXT,
|
|
blacklisted_by TEXT NOT NULL DEFAULT '',
|
|
created_at DATETIME NOT NULL
|
|
);`,
|
|
}
|
|
|
|
migrations = append(migrations,
|
|
// v4: embed image fields on panel_types
|
|
`ALTER TABLE panel_types ADD COLUMN thumbnail_url TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE panel_types ADD COLUMN image_url TEXT NOT NULL DEFAULT '';`,
|
|
// v5: embed image/thumbnail on panel_configs (panel-level)
|
|
`ALTER TABLE panel_configs ADD COLUMN embed_image TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE panel_configs ADD COLUMN embed_thumbnail TEXT NOT NULL DEFAULT '';`,
|
|
// v6: per-ticket guild + channel routing + convoc panel config
|
|
`ALTER TABLE tickets ADD COLUMN guild_id TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE tickets ADD COLUMN claim_channel_id TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE tickets ADD COLUMN log_channel_id TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE convocation_config ADD COLUMN panel_channel_id TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE convocation_config ADD COLUMN panel_message_id TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE convocation_config ADD COLUMN panel_embed_title TEXT NOT NULL DEFAULT 'Créer une convocation';
|
|
ALTER TABLE convocation_config ADD COLUMN panel_embed_description TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE convocation_config ADD COLUMN panel_embed_color TEXT NOT NULL DEFAULT '#5865f2';`,
|
|
// v7: per-ticket staff role + reup time, per-type reup time, convocation panels table
|
|
`ALTER TABLE tickets ADD COLUMN staff_role_id TEXT NOT NULL DEFAULT '';
|
|
ALTER TABLE tickets ADD COLUMN claim_reup_minutes INTEGER NOT NULL DEFAULT 0;
|
|
ALTER TABLE panel_types ADD COLUMN claim_reup_minutes INTEGER NOT NULL DEFAULT 0;
|
|
CREATE TABLE IF NOT EXISTS convocation_panels (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
guild_id TEXT NOT NULL,
|
|
embed_title TEXT NOT NULL DEFAULT 'Créer une convocation',
|
|
embed_description TEXT NOT NULL DEFAULT '',
|
|
embed_color TEXT NOT NULL DEFAULT '#5865f2',
|
|
channel_id TEXT NOT NULL DEFAULT '',
|
|
message_id TEXT NOT NULL DEFAULT '',
|
|
created_at DATETIME NOT NULL,
|
|
updated_at DATETIME NOT NULL
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_convoc_panels_guild ON convocation_panels(guild_id);`,
|
|
)
|
|
|
|
for i, m := range migrations {
|
|
v := i + 1
|
|
if v <= version {
|
|
continue
|
|
}
|
|
if _, err := db.ExecContext(ctx, m); err != nil {
|
|
return fmt.Errorf("migration v%d: %w", v, err)
|
|
}
|
|
if _, err := db.ExecContext(ctx, `INSERT INTO schema_version(version) VALUES(?)`, v); err != nil {
|
|
return fmt.Errorf("record migration v%d: %w", v, err)
|
|
}
|
|
}
|
|
return nil
|
|
}
|