church-api/migrations/20250627000001_complete_schema.sql

-- Complete Church API Schema
-- Drop existing tables if they exist (except users which has data)
DROP TABLE IF EXISTS pending_events CASCADE;
DROP TABLE IF EXISTS events CASCADE;
DROP TABLE IF EXISTS bulletins CASCADE;
DROP TABLE IF EXISTS church_config CASCADE;
DROP TABLE IF EXISTS schedules CASCADE;
DROP TABLE IF EXISTS bible_verses CASCADE;
DROP TABLE IF EXISTS app_versions CASCADE;

-- Update users table to add missing columns
ALTER TABLE users ADD COLUMN IF NOT EXISTS name VARCHAR(255);
ALTER TABLE users ADD COLUMN IF NOT EXISTS avatar_url VARCHAR(500);
ALTER TABLE users ADD COLUMN IF NOT EXISTS verified BOOLEAN DEFAULT false;
ALTER TABLE users ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();
ALTER TABLE users ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();

-- Church configuration
CREATE TABLE church_config (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    church_name VARCHAR(100) NOT NULL,
    contact_email VARCHAR(255) NOT NULL,
    contact_phone VARCHAR(20),
    church_address TEXT NOT NULL,
    po_box VARCHAR(100),
    google_maps_url VARCHAR(500),
    about_text TEXT NOT NULL,
    api_keys JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Bulletins
CREATE TABLE bulletins (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(255) NOT NULL,
    date DATE NOT NULL,
    url VARCHAR(500),
    pdf_url VARCHAR(500),
    is_active BOOLEAN DEFAULT true,
    pdf_file VARCHAR(500),
    sabbath_school TEXT,
    divine_worship TEXT,
    scripture_reading TEXT,
    sunset TEXT,
    cover_image VARCHAR(500),
    pdf_path VARCHAR(500),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Events
CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    location VARCHAR(255) NOT NULL,
    location_url VARCHAR(500),
    image VARCHAR(500),
    thumbnail VARCHAR(500),
    category VARCHAR(20) CHECK (category IN ('Service', 'Social', 'Ministry', 'Other')) NOT NULL,
    is_featured BOOLEAN DEFAULT false,
    recurring_type VARCHAR(20) CHECK (recurring_type IN ('DAILY', 'WEEKLY', 'BIWEEKLY', 'FIRST_TUESDAY')),
    approved_from VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Pending events (for approval workflow)
CREATE TABLE pending_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    location VARCHAR(255) NOT NULL,
    location_url VARCHAR(500),
    image VARCHAR(500),
    thumbnail VARCHAR(500),
    category VARCHAR(20) CHECK (category IN ('Service', 'Social', 'Ministry', 'Other')) NOT NULL,
    is_featured BOOLEAN DEFAULT false,
    recurring_type VARCHAR(20) CHECK (recurring_type IN ('DAILY', 'WEEKLY', 'BIWEEKLY', 'FIRST_TUESDAY')),
    approval_status VARCHAR(20) DEFAULT 'pending' CHECK (approval_status IN ('pending', 'approved', 'rejected')),
    submitted_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    bulletin_week VARCHAR(10) CHECK (bulletin_week IN ('current', 'next')) NOT NULL,
    admin_notes TEXT,
    submitter_email VARCHAR(255),
    email_sent BOOLEAN DEFAULT false,
    pending_email_sent BOOLEAN DEFAULT false,
    rejection_email_sent BOOLEAN DEFAULT false,
    approval_email_sent BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Schedules (offering times, sunset times, quarterly schedules)
CREATE TABLE schedules (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    schedule_type VARCHAR(50) NOT NULL,
    year INTEGER,
    quarter INTEGER,
    schedule_data JSONB NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Bible verses storage
CREATE TABLE bible_verses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    verses JSONB NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Mobile app versions
CREATE TABLE app_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    platform VARCHAR(20) NOT NULL,
    version_name VARCHAR(50),
    version_code INTEGER,
    download_url VARCHAR(500),
    update_required BOOLEAN DEFAULT false,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes
CREATE INDEX idx_bulletins_date ON bulletins(date DESC);
CREATE INDEX idx_bulletins_active ON bulletins(is_active) WHERE is_active = true;
CREATE INDEX idx_events_start_time ON events(start_time);
CREATE INDEX idx_events_featured ON events(is_featured) WHERE is_featured = true;
CREATE INDEX idx_events_category ON events(category);
CREATE INDEX idx_pending_events_status ON pending_events(approval_status);
CREATE INDEX idx_schedules_type_year ON schedules(schedule_type, year);

-- Insert default church config
INSERT INTO church_config (church_name, contact_email, church_address, about_text) VALUES 
('Rockville Tolland SDA Church', 'admin@rockvilletollandsda.church', '123 Church Street, Tolland, CT', 'Welcome to our church community.');