🗄️ CJOStats Database Schema

-- CJOStats v3 Database Schema
-- PostgreSQL schema for airline pilot application tracking and interview prep

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ============================================================
-- CORE TABLES
-- ============================================================

-- Airlines table
CREATE TABLE airlines (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(10) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    logo_url TEXT,
    website_url TEXT,
    hiring_status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Hiring paths for different positions at each airline
CREATE TABLE hiring_paths (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    airline_id UUID NOT NULL REFERENCES airlines(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    position_type VARCHAR(100) NOT NULL, -- 'pilot', 'first_officer', 'captain', etc.
    description TEXT,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(airline_id, name)
);

-- Steps in the hiring process for each airline/path
CREATE TABLE airline_hiring_steps (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    airline_id UUID NOT NULL REFERENCES airlines(id) ON DELETE CASCADE,
    hiring_path_id UUID REFERENCES hiring_paths(id) ON DELETE CASCADE,
    step_name VARCHAR(255) NOT NULL,
    step_order INTEGER NOT NULL,
    description TEXT,
    estimated_duration_days INTEGER,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(airline_id, step_order)
);

-- User profiles (extends Supabase auth.users)
CREATE TABLE profiles (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    username TEXT UNIQUE NOT NULL,
    flight_times JSONB DEFAULT '{}',
    education TEXT,
    employer TEXT,
    preferred_airline_id UUID REFERENCES airlines(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT username_immutable CHECK (username ~ '^[a-z0-9]{8}$')
);

-- User applications tracking
CREATE TABLE applications (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    airline_id UUID NOT NULL REFERENCES airlines(id),
    hiring_path_id UUID REFERENCES hiring_paths(id),
    outcome VARCHAR(50) DEFAULT 'in_progress',
    application_date DATE,
    interview_date DATE,
    class_date DATE,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CHECK (outcome IN ('in_progress', 'cjo', 'tbnt', 'withdrawn'))
);

-- Progress tracking for application steps
CREATE TABLE application_step_progress (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
    step_id UUID NOT NULL REFERENCES airline_hiring_steps(id),
    completed_date DATE,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- PIREPs (Pilot Reports) - post-interview feedback
CREATE TABLE pireps (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    application_id UUID NOT NULL REFERENCES applications(id) ON DELETE CASCADE,
    airline_id UUID NOT NULL REFERENCES airlines(id),
    status VARCHAR(50) DEFAULT 'pending_review',
    summary TEXT,
    difficulty_rating INTEGER CHECK (difficulty_rating >= 1 AND difficulty_rating <= 5),
    style_rating INTEGER CHECK (style_rating >= 1 AND style_rating <= 5),
    overall_experience TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    approved_at TIMESTAMPTZ,
    CONSTRAINT pirep_status_check CHECK (status IN ('pending_review', 'approved', 'rejected'))
);

-- Question categories for interview questions
CREATE TABLE question_categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    parent_id UUID REFERENCES question_categories(id),
    name VARCHAR(255) NOT NULL,
    code VARCHAR(10) UNIQUE NOT NULL,
    description TEXT,
    depth INTEGER DEFAULT 0,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Interview questions from PIREPs
CREATE TABLE interview_questions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    pirep_id UUID NOT NULL REFERENCES pireps(id) ON DELETE CASCADE,
    airline_id UUID NOT NULL REFERENCES airlines(id),
    question_category_id UUID REFERENCES question_categories(id),
    submitted_text TEXT NOT NULL,
    approved_text TEXT,
    status VARCHAR(50) DEFAULT 'pending_review',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    approved_at TIMESTAMPTZ,
    CONSTRAINT question_status_check CHECK (status IN ('pending_review', 'approved', 'rejected'))
);

-- Practice sessions for interview prep
CREATE TABLE practice_sessions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    airline_id UUID NOT NULL REFERENCES airlines(id),
    started_at TIMESTAMPTZ DEFAULT NOW(),
    ended_at TIMESTAMPTZ,
    question_count INTEGER DEFAULT 0,
    session_type VARCHAR(50) DEFAULT 'random',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- User subscriptions (Stripe integration)
CREATE TABLE subscriptions (
    user_id UUID PRIMARY KEY REFERENCES profiles(id) ON DELETE CASCADE,
    stripe_customer_id TEXT UNIQUE,
    stripe_subscription_id TEXT UNIQUE,
    status VARCHAR(50) NOT NULL,
    current_period_end TIMESTAMPTZ,
    trial_end TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT subscription_status_check CHECK (status IN ('active', 'past_due', 'canceled', 'incomplete', 'trialing'))
);

-- User roles for RBAC
CREATE TABLE user_roles (
    user_id UUID PRIMARY KEY REFERENCES profiles(id) ON DELETE CASCADE,
    role VARCHAR(50) DEFAULT 'user',
    granted_by UUID REFERENCES profiles(id),
    granted_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT role_check CHECK (role IN ('user', 'moderator', 'admin'))
);

-- Function to check if this is the first user and assign admin role
CREATE OR REPLACE FUNCTION assign_admin_role_if_first_user()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if this is the first user in the system
    IF (SELECT COUNT(*) FROM user_roles) = 0 THEN
        -- First user gets admin role
        INSERT INTO user_roles (user_id, role, granted_at, updated_at)
        VALUES (NEW.id, 'admin', NOW(), NOW());
    ELSE
        -- Subsequent users get default user role
        INSERT INTO user_roles (user_id, role, granted_at, updated_at)
        VALUES (NEW.id, 'user', NOW(), NOW());
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically assign role when profile is created
DROP TRIGGER IF EXISTS assign_user_role ON profiles;
CREATE TRIGGER assign_user_role
    AFTER INSERT ON profiles
    FOR EACH ROW
    EXECUTE FUNCTION assign_admin_role_if_first_user();

-- ============================================================
-- INDEXES
-- ============================================================

CREATE INDEX idx_profiles_username ON profiles(username);
CREATE INDEX idx_applications_user_id ON applications(user_id);
CREATE INDEX idx_applications_airline_id ON applications(airline_id);
CREATE INDEX idx_applications_outcome ON applications(outcome);
CREATE INDEX idx_pireps_airline_id ON pireps(airline_id);
CREATE INDEX idx_pireps_status ON pireps(status);
CREATE INDEX idx_interview_questions_airline_id ON interview_questions(airline_id);
CREATE INDEX idx_interview_questions_status ON interview_questions(status);
CREATE INDEX idx_practice_sessions_user_id ON practice_sessions(user_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);

-- ============================================================
-- FUNCTIONS AND TRIGGERS
-- ============================================================

-- Function to generate random username
CREATE OR REPLACE FUNCTION gen_random_username() RETURNS TEXT AS $$
BEGIN
    RETURN lower(
        substring(encode(digest(gen_random_uuid()::text, 'sha1'), 'hex') for 8)
    );
END;
$$ LANGUAGE plpgsql STABLE;

-- Function to handle new user creation
CREATE OR REPLACE FUNCTION handle_new_user() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.profiles (id, username, created_at, updated_at)
    VALUES (NEW.id, gen_random_username(), NOW(), NOW());
    
    INSERT INTO public.user_roles (user_id, role)
    VALUES (NEW.id, 'user');
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger to create profile when user signs up
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW EXECUTE FUNCTION handle_new_user();

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Triggers for updated_at
CREATE TRIGGER update_airlines_updated_at BEFORE UPDATE ON airlines FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_hiring_paths_updated_at BEFORE UPDATE ON hiring_paths FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_airline_hiring_steps_updated_at BEFORE UPDATE ON airline_hiring_steps FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_profiles_updated_at BEFORE UPDATE ON profiles FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_applications_updated_at BEFORE UPDATE ON applications FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_question_categories_updated_at BEFORE UPDATE ON question_categories FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_subscriptions_updated_at BEFORE UPDATE ON subscriptions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_user_roles_updated_at BEFORE UPDATE ON user_roles FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ============================================================
-- ROW LEVEL SECURITY (RLS)
-- ============================================================

-- Enable RLS on all tables
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE applications ENABLE ROW LEVEL SECURITY;
ALTER TABLE application_step_progress ENABLE ROW LEVEL SECURITY;
ALTER TABLE pireps ENABLE ROW LEVEL SECURITY;
ALTER TABLE interview_questions ENABLE ROW LEVEL SECURITY;
ALTER TABLE practice_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_roles ENABLE ROW LEVEL SECURITY;

-- Function to get current user role from JWT
CREATE OR REPLACE FUNCTION get_user_role()
RETURNS TEXT AS $$
BEGIN
    RETURN COALESCE(
        NULLIF(current_setting('request.jwt.claims', true), '')::jsonb ->> 'user_role',
        'user'
    );
END;
$$ LANGUAGE plpgsql STABLE;

-- RLS Policies

-- Profiles: Users can only see and update their own profile
CREATE POLICY "Users can view own profile" ON profiles
    FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update own profile" ON profiles
    FOR UPDATE USING (auth.uid() = id);

-- Applications: Users can only see their own applications
CREATE POLICY "Users can view own applications" ON applications
    FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own applications" ON applications
    FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own applications" ON applications
    FOR UPDATE USING (auth.uid() = user_id);

-- Application step progress: Users can only see their own progress
CREATE POLICY "Users can view own step progress" ON application_step_progress
    FOR SELECT USING (EXISTS (
        SELECT 1 FROM applications WHERE applications.id = application_step_progress.application_id AND applications.user_id = auth.uid()
    ));

CREATE POLICY "Users can insert own step progress" ON application_step_progress
    FOR INSERT WITH CHECK (EXISTS (
        SELECT 1 FROM applications WHERE applications.id = application_step_progress.application_id AND applications.user_id = auth.uid()
    ));

-- PIREPs: Users can only see their own PIREPs, approved PIREPs are visible to all authenticated users
CREATE POLICY "Users can view own pireps" ON pireps
    FOR SELECT USING (EXISTS (
        SELECT 1 FROM applications WHERE applications.id = pireps.application_id AND applications.user_id = auth.uid()
    ));

CREATE POLICY "Users can view approved pireps" ON pireps
    FOR SELECT USING (status = 'approved');

CREATE POLICY "Users can insert own pireps" ON pireps
    FOR INSERT WITH CHECK (EXISTS (
        SELECT 1 FROM applications WHERE applications.id = pireps.application_id AND applications.user_id = auth.uid()
    ));

-- Interview questions: Approved questions visible to all authenticated users with active subscription
CREATE POLICY "Users can view approved questions" ON interview_questions
    FOR SELECT USING (status = 'approved' AND EXISTS (
        SELECT 1 FROM subscriptions WHERE subscriptions.user_id = auth.uid() AND subscriptions.status = 'active'
    ));

CREATE POLICY "Users can insert own questions" ON interview_questions
    FOR INSERT WITH CHECK (EXISTS (
        SELECT 1 FROM pireps JOIN applications ON applications.id = pireps.application_id 
        WHERE pireps.id = interview_questions.pirep_id AND applications.user_id = auth.uid()
    ));

-- Moderation policies (for moderators and admins)
CREATE POLICY "Moderators can update questions" ON interview_questions
    FOR UPDATE USING (get_user_role() IN ('moderator', 'admin'));

CREATE POLICY "Moderators can update pireps" ON pireps
    FOR UPDATE USING (get_user_role() IN ('moderator', 'admin'));

-- Admin policies
CREATE POLICY "Admins can manage airlines" ON airlines
    FOR ALL USING (get_user_role() = 'admin');

CREATE POLICY "Admins can manage hiring paths" ON hiring_paths
    FOR ALL USING (get_user_role() = 'admin');

CREATE POLICY "Admins can manage hiring steps" ON airline_hiring_steps
    FOR ALL USING (get_user_role() = 'admin');

CREATE POLICY "Admins can manage question categories" ON question_categories
    FOR ALL USING (get_user_role() = 'admin');

CREATE POLICY "Admins can manage user roles" ON user_roles
    FOR ALL USING (get_user_role() = 'admin');

-- Practice sessions: Users can only see their own sessions
CREATE POLICY "Users can view own practice sessions" ON practice_sessions
    FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own practice sessions" ON practice_sessions
    FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Subscriptions: Users can only see their own subscription
CREATE POLICY "Users can view own subscription" ON subscriptions
    FOR SELECT USING (auth.uid() = user_id);

-- User roles: Users can see their own role
CREATE POLICY "Users can view own role" ON user_roles
    FOR SELECT USING (auth.uid() = user_id);

-- ============================================================
-- SAMPLE DATA
-- ============================================================

-- Insert sample airlines
INSERT INTO airlines (code, name) VALUES
('UAL', 'United Airlines'),
('DAL', 'Delta Air Lines'),
('AAL', 'American Airlines'),
('SWA', 'Southwest Airlines'),
('JBU', 'JetBlue Airways'),
('ALK', 'Alaska Airlines'),
('F9', 'Frontier Airlines'),
('NK', 'Spirit Airlines');

-- Insert sample question categories
INSERT INTO question_categories (code, name, description) VALUES
('ITD', 'Interpersonal & Crew Dynamics', 'Questions focusing on interactions with crew members and colleagues, including teamwork, conflict resolution, communication, and Crew Resource Management (CRM).'),
('JDM', 'Judgment & Decision Making', 'Scenarios focused on the process of making and defending decisions, especially when faced with incomplete information, disagreement, or criticism.'),
('LNI', 'Leadership & Initiative', 'Questions exploring leadership experiences, personal accomplishments, mentoring, and driving positive change or improvement within an organization.'),
('PIA', 'Professional Integrity & Adherence', 'Questions assessing a pilot''s commitment to following Standard Operating Procedures (SOPs), rules, and demonstrating professional integrity.'),
('PUP', 'Performance Under Pressure', 'Scenarios involving high-stakes or challenging operational environments, such as stress, crises, time-critical situations, and external pressures.');

-- Insert sample hiring paths
INSERT INTO hiring_paths (airline_id, name, position_type) 
SELECT a.id, 'First Officer', 'first_officer' FROM airlines a WHERE a.code = 'UAL'
UNION ALL
SELECT a.id, 'Captain', 'captain' FROM airlines a WHERE a.code = 'UAL'
UNION ALL
SELECT a.id, 'First Officer', 'first_officer' FROM airlines a WHERE a.code = 'DAL'
UNION ALL
SELECT a.id, 'Captain', 'captain' FROM airlines a WHERE a.code = 'DAL';

-- Insert sample hiring steps
INSERT INTO airline_hiring_steps (airline_id, step_name, step_order, description, estimated_duration_days)
SELECT a.id, 'Application Submitted', 1, 'Initial application submitted', 1 FROM airlines a WHERE a.code = 'UAL'
UNION ALL
SELECT a.id, 'Hogan Assessment', 2, 'Complete Hogan assessment test', 7 FROM airlines a WHERE a.code = 'UAL'
UNION ALL
SELECT a.id, 'Phone Interview', 3, 'Initial phone screening interview', 14 FROM airlines a WHERE a.code = 'UAL'
UNION ALL
SELECT a.id, 'In-Person Interview', 4, 'Panel interview at airline headquarters', 21 FROM airlines a WHERE a.code = 'UAL'
UNION ALL
SELECT a.id, 'Background Check', 5, 'Comprehensive background verification', 30 FROM airlines a WHERE a.code = 'UAL'
UNION ALL
SELECT a.id, 'Class Date Assignment', 6, 'Training class date assigned', 45 FROM airlines a WHERE a.code = 'UAL';