Lab 11: Database Scripting & Automation¶

-- Migration: V003 — Add scholarship tracking
-- Idempotent: safe to run multiple times
-- Author: [Your Name]
-- Date: [Date]

SET search_path = fsu;

-- Version tracking table (create if not exists)
CREATE TABLE IF NOT EXISTS schema_migrations (
    version     VARCHAR(20) PRIMARY KEY,
    description TEXT,
    applied_at  TIMESTAMPTZ DEFAULT NOW(),
    applied_by  TEXT DEFAULT current_user
);

-- Guard: skip if already applied
DO $$
BEGIN
    IF EXISTS(SELECT 1 FROM schema_migrations WHERE version = 'V003') THEN
        RAISE NOTICE 'Migration V003 already applied — skipping';
        RETURN;
    END IF;

    -- Create scholarships table
    CREATE TABLE IF NOT EXISTS scholarships (
        scholarship_id SERIAL       PRIMARY KEY,
        name           VARCHAR(100) NOT NULL,
        amount         NUMERIC(10,2) NOT NULL CHECK (amount > 0),
        dept_id        INT          REFERENCES departments(dept_id),
        min_gpa        NUMERIC(3,2) DEFAULT 3.0,
        created_at     TIMESTAMPTZ  DEFAULT NOW()
    );

    -- Create student_scholarships junction table
    CREATE TABLE IF NOT EXISTS student_scholarships (
        student_id     INT NOT NULL REFERENCES students(student_id),
        scholarship_id INT NOT NULL REFERENCES scholarships(scholarship_id),
        awarded_year   SMALLINT NOT NULL,
        amount_awarded NUMERIC(10,2),
        PRIMARY KEY (student_id, scholarship_id, awarded_year)
    );

    -- Add index for scholarship lookups by department
    CREATE INDEX IF NOT EXISTS idx_scholarships_dept ON scholarships(dept_id);

    -- Seed initial scholarships
    INSERT INTO scholarships (name, amount, dept_id, min_gpa) VALUES
        ('Dean''s Excellence Award', 5000.00, NULL, 3.8),
        ('CS Department Award',      2500.00, 1,    3.5),
        ('Math Department Award',    2500.00, 2,    3.5),
        ('General Merit Award',      1000.00, NULL, 3.0)
    ON CONFLICT DO NOTHING;

    -- Record migration as applied
    INSERT INTO schema_migrations (version, description)
    VALUES ('V003', 'Add scholarship and student_scholarships tables');

    RAISE NOTICE 'Migration V003 applied successfully';
END;
$$;

-- Verify
SELECT version, description, applied_at FROM schema_migrations ORDER BY version;

CREATE OR REPLACE FUNCTION db_health_report()
RETURNS TABLE(check_name TEXT, value TEXT, status TEXT) AS $$
BEGIN
    -- Student count
    RETURN QUERY SELECT 'Student count'::TEXT,
        (SELECT COUNT(*)::TEXT FROM students),
        CASE WHEN (SELECT COUNT(*) FROM students) >= 500 THEN 'OK' ELSE 'LOW' END;

    -- GPA anomalies
    RETURN QUERY SELECT 'Invalid GPA count'::TEXT,
        (SELECT COUNT(*)::TEXT FROM students WHERE gpa > 4.0 OR gpa < 0),
        CASE WHEN (SELECT COUNT(*) FROM students WHERE gpa > 4.0 OR gpa < 0) = 0
             THEN 'OK' ELSE 'ERROR' END;

    -- Orphaned enrollments
    RETURN QUERY SELECT 'Orphaned enrollments'::TEXT,
        (SELECT COUNT(*)::TEXT FROM enrollments e
         WHERE NOT EXISTS(SELECT 1 FROM students s WHERE s.student_id = e.student_id)),
        CASE WHEN (SELECT COUNT(*) FROM enrollments e
                   WHERE NOT EXISTS(SELECT 1 FROM students s WHERE s.student_id=e.student_id)) = 0
             THEN 'OK' ELSE 'ERROR' END;

    -- Index count
    RETURN QUERY SELECT 'Non-PK index count'::TEXT,
        (SELECT COUNT(*)::TEXT FROM pg_indexes WHERE schemaname='fsu' AND indexname NOT LIKE '%pkey%'),
        CASE WHEN (SELECT COUNT(*) FROM pg_indexes WHERE schemaname='fsu' AND indexname NOT LIKE '%pkey%') >= 3
             THEN 'OK' ELSE 'LOW' END;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM db_health_report();

SET search_path = fsu;

CREATE OR REPLACE FUNCTION verify_lab11()
RETURNS TABLE(check_id TEXT, description TEXT, result TEXT, points INT) AS $$
BEGIN
    RETURN QUERY SELECT '01', 'schema_migrations table exists',
        CASE WHEN EXISTS(SELECT 1 FROM information_schema.tables
                         WHERE table_schema='fsu' AND table_name='schema_migrations')
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '02', 'V003 migration applied',
        CASE WHEN EXISTS(SELECT 1 FROM schema_migrations WHERE version='V003')
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '03', 'scholarships table exists',
        CASE WHEN EXISTS(SELECT 1 FROM information_schema.tables
                         WHERE table_schema='fsu' AND table_name='scholarships')
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '04', 'scholarships has 4 seed rows',
        CASE WHEN (SELECT COUNT(*) FROM scholarships) = 4
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '05', 'student_scholarships table exists',
        CASE WHEN EXISTS(SELECT 1 FROM information_schema.tables
                         WHERE table_schema='fsu' AND table_name='student_scholarships')
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '06', 'db_health_report function exists',
        CASE WHEN EXISTS(SELECT 1 FROM pg_proc WHERE proname='db_health_report')
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '07', 'db_health_report returns rows',
        CASE WHEN (SELECT COUNT(*) FROM db_health_report()) >= 4
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '08', 'No orphaned enrollments detected',
        CASE WHEN (
            SELECT value FROM db_health_report() WHERE check_name='Orphaned enrollments'
        ) = '0' THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '09', 'idx_scholarships_dept index exists',
        CASE WHEN EXISTS(SELECT 1 FROM pg_indexes WHERE indexname='idx_scholarships_dept')
             THEN 'PASS' ELSE 'FAIL' END, 10;

    RETURN QUERY SELECT '10', 'Students table has 500+ rows',
        CASE WHEN (SELECT COUNT(*) FROM students) >= 500
             THEN 'PASS' ELSE 'FAIL' END, 10;
END;
$$ LANGUAGE plpgsql;

SELECT check_id, description, result,
       CASE WHEN result = 'PASS' THEN points ELSE 0 END AS earned
FROM verify_lab11()
ORDER BY check_id;