membership-be/migrations/000_initial_schema.sql

-- ============================================================================
-- Migration 000: Initial Database Schema
-- ============================================================================
-- Description: Creates all base tables, enums, and indexes for the LOAF
--              membership platform. This migration should be run first on
--              a fresh database.
-- Date: 2024-12-18
-- Author: LOAF Development Team
-- ============================================================================

BEGIN;

-- ============================================================================
-- SECTION 1: Create ENUM Types
-- ============================================================================

-- User status enum
CREATE TYPE userstatus AS ENUM (
    'pending_email',
    'pending_validation',
    'pre_validated',
    'payment_pending',
    'active',
    'inactive',
    'canceled',
    'expired',
    'abandoned',
    'rejected'
);

-- User role enum
CREATE TYPE userrole AS ENUM (
    'guest',
    'member',
    'admin',
    'finance',
    'superadmin'
);

-- RSVP status enum
CREATE TYPE rsvpstatus AS ENUM (
    'yes',
    'no',
    'maybe'
);

-- Subscription status enum
CREATE TYPE subscriptionstatus AS ENUM (
    'active',
    'cancelled',
    'expired'
);

-- Donation type enum
CREATE TYPE donationtype AS ENUM (
    'member',
    'public'
);

-- Donation status enum
CREATE TYPE donationstatus AS ENUM (
    'pending',
    'completed',
    'failed'
);

-- Invitation status enum
CREATE TYPE invitationstatus AS ENUM (
    'pending',
    'accepted',
    'expired',
    'revoked'
);

-- Import job status enum
CREATE TYPE importjobstatus AS ENUM (
    'processing',
    'completed',
    'failed',
    'partial'
);

COMMIT;

-- Display progress
SELECT 'Step 1/8 completed: ENUM types created' AS progress;

BEGIN;

-- ============================================================================
-- SECTION 2: Create Core Tables
-- ============================================================================

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Authentication
    email VARCHAR NOT NULL UNIQUE,
    password_hash VARCHAR NOT NULL,
    email_verified BOOLEAN NOT NULL DEFAULT FALSE,
    email_verification_token VARCHAR UNIQUE,

    -- Personal Information
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    phone VARCHAR,
    address VARCHAR,
    city VARCHAR,
    state VARCHAR(2),
    zipcode VARCHAR(10),
    date_of_birth DATE,
    bio TEXT,

    -- Profile
    profile_photo_url VARCHAR,

    -- Social Media
    social_media_facebook VARCHAR,
    social_media_instagram VARCHAR,
    social_media_twitter VARCHAR,
    social_media_linkedin VARCHAR,

    -- Partner Information
    partner_first_name VARCHAR,
    partner_last_name VARCHAR,
    partner_is_member BOOLEAN DEFAULT FALSE,
    partner_plan_to_become_member BOOLEAN DEFAULT FALSE,

    -- Referral
    referred_by_member_name VARCHAR,
    lead_sources JSONB DEFAULT '[]'::jsonb,

    -- Status & Role
    status userstatus NOT NULL DEFAULT 'pending_email',
    role userrole NOT NULL DEFAULT 'guest',
    role_id UUID,  -- For dynamic RBAC (added in later migration)

    -- Rejection Tracking
    rejection_reason TEXT,
    rejected_at TIMESTAMP WITH TIME ZONE,
    rejected_by UUID REFERENCES users(id),

    -- Membership
    member_since DATE,
    tos_accepted BOOLEAN DEFAULT FALSE,
    tos_accepted_at TIMESTAMP WITH TIME ZONE,
    newsletter_subscribed BOOLEAN DEFAULT TRUE,

    -- Reminder Tracking
    reminder_30_days_sent BOOLEAN DEFAULT FALSE,
    reminder_60_days_sent BOOLEAN DEFAULT FALSE,
    reminder_85_days_sent BOOLEAN DEFAULT FALSE,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Events table
CREATE TABLE IF NOT EXISTS events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Event Details
    title VARCHAR NOT NULL,
    description TEXT,
    location VARCHAR,
    cover_image_url VARCHAR,

    -- Schedule
    start_at TIMESTAMP WITH TIME ZONE NOT NULL,
    end_at TIMESTAMP WITH TIME ZONE,

    -- Capacity
    capacity INTEGER,
    published BOOLEAN NOT NULL DEFAULT FALSE,

    -- Calendar Integration
    calendar_uid VARCHAR UNIQUE,
    microsoft_calendar_id VARCHAR,
    microsoft_calendar_sync_enabled BOOLEAN DEFAULT FALSE,

    -- Metadata
    created_by UUID REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Event RSVPs table
CREATE TABLE IF NOT EXISTS event_rsvps (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- RSVP Details
    rsvp_status rsvpstatus NOT NULL DEFAULT 'maybe',
    attended BOOLEAN DEFAULT FALSE,
    attended_at TIMESTAMP WITH TIME ZONE,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- Unique constraint: one RSVP per user per event
    UNIQUE(event_id, user_id)
);

-- Event Gallery table
CREATE TABLE IF NOT EXISTS event_galleries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,

    -- Image Details
    image_url VARCHAR NOT NULL,
    caption TEXT,
    order_index INTEGER DEFAULT 0,

    -- Metadata
    uploaded_by UUID REFERENCES users(id),
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

COMMIT;

-- Display progress
SELECT 'Step 2/8 completed: Core tables (users, events, rsvps, gallery) created' AS progress;

BEGIN;

-- ============================================================================
-- SECTION 3: Create Subscription & Payment Tables
-- ============================================================================

-- Subscription Plans table
CREATE TABLE IF NOT EXISTS subscription_plans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Plan Details
    name VARCHAR NOT NULL,
    description TEXT,
    price_cents INTEGER NOT NULL,
    billing_cycle VARCHAR NOT NULL DEFAULT 'annual',

    -- Configuration
    active BOOLEAN NOT NULL DEFAULT TRUE,
    features JSONB DEFAULT '[]'::jsonb,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Subscriptions table
CREATE TABLE IF NOT EXISTS subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    plan_id UUID NOT NULL REFERENCES subscription_plans(id),

    -- Stripe Integration
    stripe_subscription_id VARCHAR,
    stripe_customer_id VARCHAR,

    -- Status & Dates
    status subscriptionstatus DEFAULT 'active',
    start_date TIMESTAMP WITH TIME ZONE NOT NULL,
    end_date TIMESTAMP WITH TIME ZONE,
    next_billing_date TIMESTAMP WITH TIME ZONE,

    -- Payment Details
    amount_paid_cents INTEGER,
    base_subscription_cents INTEGER NOT NULL,
    donation_cents INTEGER DEFAULT 0 NOT NULL,

    -- Manual Payment Support
    manual_payment BOOLEAN DEFAULT FALSE NOT NULL,
    manual_payment_notes TEXT,
    manual_payment_admin_id UUID REFERENCES users(id),
    manual_payment_date TIMESTAMP WITH TIME ZONE,
    payment_method VARCHAR,

    -- Timestamps
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Donations table
CREATE TABLE IF NOT EXISTS donations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Donation Details
    amount_cents INTEGER NOT NULL,
    donation_type donationtype NOT NULL DEFAULT 'public',
    status donationstatus NOT NULL DEFAULT 'pending',

    -- Donor Information
    user_id UUID REFERENCES users(id),  -- NULL for public donations
    donor_email VARCHAR,
    donor_name VARCHAR,

    -- Payment Details
    stripe_checkout_session_id VARCHAR,
    stripe_payment_intent_id VARCHAR,
    payment_method VARCHAR,

    -- Metadata
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE
);

COMMIT;

-- Display progress
SELECT 'Step 3/8 completed: Subscription and donation tables created' AS progress;

BEGIN;

-- ============================================================================
-- SECTION 4: Create RBAC Tables
-- ============================================================================

-- Permissions table
CREATE TABLE IF NOT EXISTS permissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    code VARCHAR NOT NULL UNIQUE,
    name VARCHAR NOT NULL,
    description TEXT,
    module VARCHAR NOT NULL,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Roles table (for dynamic RBAC)
CREATE TABLE IF NOT EXISTS roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    code VARCHAR NOT NULL UNIQUE,
    name VARCHAR NOT NULL,
    description TEXT,
    is_system_role BOOLEAN NOT NULL DEFAULT FALSE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id) ON DELETE SET NULL
);

-- Role Permissions junction table
CREATE TABLE IF NOT EXISTS role_permissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    role userrole,  -- Legacy enum-based role (for backward compatibility)
    role_id UUID REFERENCES roles(id) ON DELETE CASCADE,  -- Dynamic role
    permission_id UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id) ON DELETE SET NULL
);

COMMIT;

-- Display progress
SELECT 'Step 4/8 completed: RBAC tables created' AS progress;

BEGIN;

-- ============================================================================
-- SECTION 5: Create Document Management Tables
-- ============================================================================

-- Newsletter Archive table
CREATE TABLE IF NOT EXISTS newsletter_archives (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    title VARCHAR NOT NULL,
    file_url VARCHAR NOT NULL,
    file_size_bytes INTEGER,
    issue_date DATE NOT NULL,
    description TEXT,

    uploaded_by UUID REFERENCES users(id),
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Financial Reports table
CREATE TABLE IF NOT EXISTS financial_reports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    title VARCHAR NOT NULL,
    file_url VARCHAR NOT NULL,
    file_size_bytes INTEGER,
    fiscal_period VARCHAR NOT NULL,
    report_type VARCHAR,

    uploaded_by UUID REFERENCES users(id),
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Bylaws Documents table
CREATE TABLE IF NOT EXISTS bylaws_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    title VARCHAR NOT NULL,
    file_url VARCHAR NOT NULL,
    file_size_bytes INTEGER,
    version VARCHAR NOT NULL,
    effective_date DATE NOT NULL,
    description TEXT,
    is_current BOOLEAN DEFAULT TRUE,

    uploaded_by UUID REFERENCES users(id),
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

COMMIT;

-- Display progress
SELECT 'Step 5/8 completed: Document management tables created' AS progress;

BEGIN;

-- ============================================================================
-- SECTION 6: Create System Tables
-- ============================================================================

-- Storage Usage table
CREATE TABLE IF NOT EXISTS storage_usage (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    total_bytes_used BIGINT NOT NULL DEFAULT 0,
    max_bytes_allowed BIGINT NOT NULL DEFAULT 10737418240,  -- 10GB
    last_updated TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- User Invitations table
CREATE TABLE IF NOT EXISTS user_invitations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    email VARCHAR NOT NULL,
    token VARCHAR NOT NULL UNIQUE,
    role userrole NOT NULL,
    status invitationstatus NOT NULL DEFAULT 'pending',

    invited_by UUID REFERENCES users(id) ON DELETE SET NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    accepted_at TIMESTAMP WITH TIME ZONE,
    revoked_at TIMESTAMP WITH TIME ZONE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Import Jobs table
CREATE TABLE IF NOT EXISTS import_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    filename VARCHAR NOT NULL,
    status importjobstatus NOT NULL DEFAULT 'processing',
    total_rows INTEGER DEFAULT 0,
    processed_rows INTEGER DEFAULT 0,
    success_count INTEGER DEFAULT 0,
    error_count INTEGER DEFAULT 0,
    error_log JSONB DEFAULT '[]'::jsonb,

    started_by UUID REFERENCES users(id),
    started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP WITH TIME ZONE
);

COMMIT;

-- Display progress
SELECT 'Step 6/8 completed: System tables created' AS progress;

BEGIN;

-- ============================================================================
-- SECTION 7: Create Indexes
-- ============================================================================

-- Users table indexes
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_users_role_id ON users(role_id);
CREATE INDEX IF NOT EXISTS idx_users_email_verified ON users(email_verified);
CREATE INDEX IF NOT EXISTS idx_users_rejected_at ON users(rejected_at) WHERE rejected_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_users_created_at ON users(created_at);

-- Events table indexes
CREATE INDEX IF NOT EXISTS idx_events_created_by ON events(created_by);
CREATE INDEX IF NOT EXISTS idx_events_start_at ON events(start_at);
CREATE INDEX IF NOT EXISTS idx_events_published ON events(published);

-- Event RSVPs indexes
CREATE INDEX IF NOT EXISTS idx_event_rsvps_event_id ON event_rsvps(event_id);
CREATE INDEX IF NOT EXISTS idx_event_rsvps_user_id ON event_rsvps(user_id);
CREATE INDEX IF NOT EXISTS idx_event_rsvps_rsvp_status ON event_rsvps(rsvp_status);

-- Event Gallery indexes
CREATE INDEX IF NOT EXISTS idx_event_galleries_event_id ON event_galleries(event_id);

-- Subscriptions indexes
CREATE INDEX IF NOT EXISTS idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_plan_id ON subscriptions(plan_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON subscriptions(status);
CREATE INDEX IF NOT EXISTS idx_subscriptions_stripe_subscription_id ON subscriptions(stripe_subscription_id);

-- Donations indexes
CREATE INDEX IF NOT EXISTS idx_donation_user ON donations(user_id);
CREATE INDEX IF NOT EXISTS idx_donation_type ON donations(donation_type);
CREATE INDEX IF NOT EXISTS idx_donation_status ON donations(status);
CREATE INDEX IF NOT EXISTS idx_donation_created ON donations(created_at);

-- Permissions indexes
CREATE INDEX IF NOT EXISTS idx_permissions_code ON permissions(code);
CREATE INDEX IF NOT EXISTS idx_permissions_module ON permissions(module);

-- Roles indexes
CREATE INDEX IF NOT EXISTS idx_roles_code ON roles(code);
CREATE INDEX IF NOT EXISTS idx_roles_is_system_role ON roles(is_system_role);

-- Role Permissions indexes
CREATE INDEX IF NOT EXISTS idx_role_permissions_role ON role_permissions(role);
CREATE INDEX IF NOT EXISTS idx_role_permissions_role_id ON role_permissions(role_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_role_permission ON role_permissions(role, permission_id) WHERE role IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_dynamic_role_permission ON role_permissions(role_id, permission_id) WHERE role_id IS NOT NULL;

COMMIT;

-- Display progress
SELECT 'Step 7/8 completed: Indexes created' AS progress;

BEGIN;

-- ============================================================================
-- SECTION 8: Initialize Default Data
-- ============================================================================

-- Insert initial storage usage record
INSERT INTO storage_usage (id, total_bytes_used, max_bytes_allowed, last_updated)
SELECT
    gen_random_uuid(),
    0,
    10737418240,  -- 10GB
    CURRENT_TIMESTAMP
WHERE NOT EXISTS (SELECT 1 FROM storage_usage);

COMMIT;

-- Display progress
SELECT 'Step 8/8 completed: Default data initialized' AS progress;

-- ============================================================================
-- Migration Complete
-- ============================================================================

SELECT '
================================================================================
✅ Migration 000 completed successfully!
================================================================================

Database schema initialized with:
- 10 ENUM types
- 17 tables (users, events, subscriptions, donations, RBAC, documents, system)
- 30+ indexes for performance
- 1 storage usage record

Next steps:
1. Run: python seed_permissions_rbac.py  (to populate permissions and roles)
2. Run: python create_admin.py           (to create superadmin user)
3. Run remaining migrations in sequence  (001-010)

Database is ready for LOAF membership platform! 🎉
================================================================================
' AS migration_complete;