`database/schema.sql`

142 lines
-- Dear Bubbe Database Schema
-- PostgreSQL / SQLite compatible

CREATE TABLE IF NOT EXISTS dear_bubbe_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Source information
    original_source VARCHAR(100) CHECK (original_source IN (
        'Dear Abby',
        'Ann Landers',
        'Public Domain',
        'Bubbe Original',
        'User Submitted'
    )),
    original_date DATE,
    original_question_text TEXT,
    source_url TEXT,

    -- Bubbe transformation
    bubbe_rewritten_question TEXT NOT NULL,
    bubbe_answer TEXT NOT NULL,

    -- Categorization
    category VARCHAR(50) CHECK (category IN (
        'family',
        'romance',
        'dating',
        'marriage',
        'health',
        'money',
        'etiquette',
        'work',
        'neighbors',
        'holidays',
        'grief',
        'kids',
        'teens',
        'pets',
        'mother-in-law',
        'cheating',
        'friendship',
        'moral-dilemma',
        'awkward',
        'misc'
    )) NOT NULL,

    tags TEXT[], -- Array of tags for additional categorization

    -- Tone/style
    tone VARCHAR(50) DEFAULT 'yiddish-grandma',
    yiddish_level VARCHAR(20) CHECK (yiddish_level IN ('light', 'medium', 'heavy')) DEFAULT 'medium',
    sarcasm_level INTEGER CHECK (sarcasm_level BETWEEN 1 AND 5) DEFAULT 3,

    -- SMS compatibility
    sms_length_ok BOOLEAN DEFAULT TRUE,
    character_count INTEGER,

    -- Scheduling
    sent_count INTEGER DEFAULT 0,
    last_sent_date TIMESTAMP,
    next_eligible_date DATE,

    -- Quality control
    approved BOOLEAN DEFAULT TRUE,
    flagged BOOLEAN DEFAULT FALSE,
    notes TEXT,

    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for performance
CREATE INDEX idx_category ON dear_bubbe_entries(category);
CREATE INDEX idx_sent_count ON dear_bubbe_entries(sent_count);
CREATE INDEX idx_next_eligible ON dear_bubbe_entries(next_eligible_date);
CREATE INDEX idx_approved ON dear_bubbe_entries(approved);

-- SMS delivery log
CREATE TABLE IF NOT EXISTS sms_delivery_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entry_id UUID REFERENCES dear_bubbe_entries(id),
    phone_number VARCHAR(20),
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delivery_status VARCHAR(20) CHECK (delivery_status IN (
        'queued',
        'sent',
        'delivered',
        'failed',
        'undelivered'
    )),
    twilio_sid VARCHAR(100),
    error_message TEXT,
    cost DECIMAL(10,4)
);

CREATE INDEX idx_sms_entry_id ON sms_delivery_log(entry_id);
CREATE INDEX idx_sms_sent_at ON sms_delivery_log(sent_at);

-- User subscriptions
CREATE TABLE IF NOT EXISTS subscribers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    timezone VARCHAR(50) DEFAULT 'America/New_York',
    delivery_time TIME DEFAULT '08:00:00',
    active BOOLEAN DEFAULT TRUE,

    -- Preferences
    categories_preference TEXT[], -- Specific categories they want
    frequency VARCHAR(20) CHECK (frequency IN ('daily', 'weekdays', 'weekly')) DEFAULT 'daily',

    -- Metadata
    subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    unsubscribed_at TIMESTAMP,
    total_messages_received INTEGER DEFAULT 0
);

CREATE INDEX idx_subscribers_active ON subscribers(active);
CREATE INDEX idx_subscribers_phone ON subscribers(phone_number);

-- User feedback
CREATE TABLE IF NOT EXISTS feedback (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entry_id UUID REFERENCES dear_bubbe_entries(id),
    subscriber_id UUID REFERENCES subscribers(id),
    rating INTEGER CHECK (rating BETWEEN 1 AND 5),
    feedback_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Statistics view
CREATE VIEW bubbe_stats AS
SELECT
    category,
    COUNT(*) as total_entries,
    SUM(sent_count) as total_sends,
    AVG(character_count) as avg_length,
    COUNT(CASE WHEN sms_length_ok THEN 1 END) as sms_compatible
FROM dear_bubbe_entries
WHERE approved = TRUE
GROUP BY category;