-- 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 $$;