88 lines
3.1 KiB
MySQL
88 lines
3.1 KiB
MySQL
|
-- Landing Database Initialization
|
||
|
|
|
||
|
|
CREATE SCHEMA IF NOT EXISTS landing;
|
||
|
|
|
||
|
|
-- Merch submissions
|
||
|
|
CREATE TABLE IF NOT EXISTS landing.merch_submissions (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
user_id UUID,
|
||
|
|
email VARCHAR(255),
|
||
|
|
type VARCHAR(50) NOT NULL,
|
||
|
|
title VARCHAR(255) NOT NULL,
|
||
|
|
description TEXT,
|
||
|
|
design_url TEXT,
|
||
|
|
image_urls TEXT[] DEFAULT '{}',
|
||
|
|
status VARCHAR(50) NOT NULL DEFAULT 'pending',
|
||
|
|
admin_notes TEXT,
|
||
|
|
votes INTEGER DEFAULT 0,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Idea voting
|
||
|
|
CREATE TABLE IF NOT EXISTS landing.idea_votes (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
submission_id UUID NOT NULL REFERENCES landing.merch_submissions(id) ON DELETE CASCADE,
|
||
|
|
user_id UUID,
|
||
|
|
session_id VARCHAR(255),
|
||
|
|
vote_type VARCHAR(20) NOT NULL DEFAULT 'up',
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
UNIQUE(submission_id, COALESCE(user_id, session_id::uuid))
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Feature requests / feedback
|
||
|
|
CREATE TABLE IF NOT EXISTS landing.feature_requests (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
user_id UUID,
|
||
|
|
email VARCHAR(255),
|
||
|
|
title VARCHAR(255) NOT NULL,
|
||
|
|
description TEXT NOT NULL,
|
||
|
|
category VARCHAR(50),
|
||
|
|
status VARCHAR(50) NOT NULL DEFAULT 'open',
|
||
|
|
priority VARCHAR(20) DEFAULT 'normal',
|
||
|
|
votes INTEGER DEFAULT 0,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Newsletter subscriptions
|
||
|
|
CREATE TABLE IF NOT EXISTS landing.newsletter_subscriptions (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
||
|
|
source VARCHAR(100) DEFAULT 'landing',
|
||
|
|
subscribed BOOLEAN DEFAULT TRUE,
|
||
|
|
verified BOOLEAN DEFAULT FALSE,
|
||
|
|
verified_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
unsubscribed_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Waitlist
|
||
|
|
CREATE TABLE IF NOT EXISTS landing.waitlist (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
||
|
|
name VARCHAR(255),
|
||
|
|
referral_code VARCHAR(50) UNIQUE,
|
||
|
|
referred_by VARCHAR(50),
|
||
|
|
position INTEGER,
|
||
|
|
invited BOOLEAN DEFAULT FALSE,
|
||
|
|
invited_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_merch_status ON landing.merch_submissions(status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_merch_votes ON landing.merch_submissions(votes DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_votes_submission ON landing.idea_votes(submission_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_features_status ON landing.feature_requests(status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_newsletter_email ON landing.newsletter_subscriptions(email);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_waitlist_position ON landing.waitlist(position);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_waitlist_referral ON landing.waitlist(referral_code);
|
||
|
|
|
||
|
|
-- Permissions
|
||
|
|
GRANT ALL PRIVILEGES ON SCHEMA landing TO lilith;
|
||
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA landing TO lilith;
|
||
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA landing TO lilith;
|
||
|
|
|
||
|
|
DO $$ BEGIN RAISE NOTICE 'Landing database initialized'; END $$;
|