72 lines
2.4 KiB
SQL
72 lines
2.4 KiB
SQL
-- Baseline legacy tables and normalize their timestamps as UTC.
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY,
|
|
username VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hashed BYTEA NOT NULL,
|
|
timezone VARCHAR(64) NOT NULL DEFAULT 'UTC',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
ALTER TABLE users
|
|
ADD COLUMN IF NOT EXISTS timezone VARCHAR(64) NOT NULL DEFAULT 'UTC';
|
|
|
|
CREATE TABLE IF NOT EXISTS notifications (
|
|
id UUID PRIMARY KEY,
|
|
user_uuid UUID REFERENCES users(id) ON DELETE CASCADE UNIQUE,
|
|
discord_webhook VARCHAR(500),
|
|
discord_enabled BOOLEAN DEFAULT FALSE,
|
|
ntfy_topic VARCHAR(255),
|
|
ntfy_enabled BOOLEAN DEFAULT FALSE,
|
|
last_message_sent TIMESTAMPTZ,
|
|
current_notification_status VARCHAR(50) DEFAULT 'inactive',
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'users'
|
|
AND column_name = 'created_at'
|
|
AND data_type = 'timestamp without time zone'
|
|
) THEN
|
|
ALTER TABLE users ALTER COLUMN created_at TYPE TIMESTAMPTZ
|
|
USING created_at AT TIME ZONE 'UTC';
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'notifications'
|
|
AND column_name = 'last_message_sent'
|
|
AND data_type = 'timestamp without time zone'
|
|
) THEN
|
|
ALTER TABLE notifications ALTER COLUMN last_message_sent TYPE TIMESTAMPTZ
|
|
USING last_message_sent AT TIME ZONE 'UTC';
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'notifications'
|
|
AND column_name = 'created_at'
|
|
AND data_type = 'timestamp without time zone'
|
|
) THEN
|
|
ALTER TABLE notifications ALTER COLUMN created_at TYPE TIMESTAMPTZ
|
|
USING created_at AT TIME ZONE 'UTC';
|
|
END IF;
|
|
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'notifications'
|
|
AND column_name = 'updated_at'
|
|
AND data_type = 'timestamp without time zone'
|
|
) THEN
|
|
ALTER TABLE notifications ALTER COLUMN updated_at TYPE TIMESTAMPTZ
|
|
USING updated_at AT TIME ZONE 'UTC';
|
|
END IF;
|
|
END $$;
|