"""Seed initial data

Revision ID: 002
Revises: 001
Create Date: 2023-07-15 14:30:00.000000

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.sql import table, column


# revision identifiers, used by Alembic.
revision = '002'
down_revision = '001'
branch_labels = None
depends_on = None


def upgrade():
    # Seed bible books data
    bible_books = table(
        'bible_books',
        column('id', sa.Integer),
        column('name', sa.String),
        column('testament', sa.Enum('Old', 'New', name='testament_enum')),
    )
    
    op.bulk_insert(
        bible_books,
        [
            # Old Testament books
            {'id': 1, 'name': 'Genesis', 'testament': 'Old'},
            {'id': 2, 'name': 'Exodus', 'testament': 'Old'},
            {'id': 3, 'name': 'Leviticus', 'testament': 'Old'},
            {'id': 4, 'name': 'Numbers', 'testament': 'Old'},
            {'id': 5, 'name': 'Deuteronomy', 'testament': 'Old'},
            {'id': 6, 'name': 'Joshua', 'testament': 'Old'},
            {'id': 7, 'name': 'Judges', 'testament': 'Old'},
            {'id': 8, 'name': 'Ruth', 'testament': 'Old'},
            {'id': 9, 'name': '1 Samuel', 'testament': 'Old'},
            {'id': 10, 'name': '2 Samuel', 'testament': 'Old'},
            {'id': 11, 'name': '1 Kings', 'testament': 'Old'},
            {'id': 12, 'name': '2 Kings', 'testament': 'Old'},
            {'id': 13, 'name': '1 Chronicles', 'testament': 'Old'},
            {'id': 14, 'name': '2 Chronicles', 'testament': 'Old'},
            {'id': 15, 'name': 'Ezra', 'testament': 'Old'},
            {'id': 16, 'name': 'Nehemiah', 'testament': 'Old'},
            {'id': 17, 'name': 'Esther', 'testament': 'Old'},
            {'id': 18, 'name': 'Job', 'testament': 'Old'},
            {'id': 19, 'name': 'Psalms', 'testament': 'Old'},
            {'id': 20, 'name': 'Proverbs', 'testament': 'Old'},
            {'id': 21, 'name': 'Ecclesiastes', 'testament': 'Old'},
            {'id': 22, 'name': 'Song of Solomon', 'testament': 'Old'},
            {'id': 23, 'name': 'Isaiah', 'testament': 'Old'},
            {'id': 24, 'name': 'Jeremiah', 'testament': 'Old'},
            {'id': 25, 'name': 'Lamentations', 'testament': 'Old'},
            {'id': 26, 'name': 'Ezekiel', 'testament': 'Old'},
            {'id': 27, 'name': 'Daniel', 'testament': 'Old'},
            {'id': 28, 'name': 'Hosea', 'testament': 'Old'},
            {'id': 29, 'name': 'Joel', 'testament': 'Old'},
            {'id': 30, 'name': 'Amos', 'testament': 'Old'},
            {'id': 31, 'name': 'Obadiah', 'testament': 'Old'},
            {'id': 32, 'name': 'Jonah', 'testament': 'Old'},
            {'id': 33, 'name': 'Micah', 'testament': 'Old'},
            {'id': 34, 'name': 'Nahum', 'testament': 'Old'},
            {'id': 35, 'name': 'Habakkuk', 'testament': 'Old'},
            {'id': 36, 'name': 'Zephaniah', 'testament': 'Old'},
            {'id': 37, 'name': 'Haggai', 'testament': 'Old'},
            {'id': 38, 'name': 'Zechariah', 'testament': 'Old'},
            {'id': 39, 'name': 'Malachi', 'testament': 'Old'},
            
            # New Testament books
            {'id': 40, 'name': 'Matthew', 'testament': 'New'},
            {'id': 41, 'name': 'Mark', 'testament': 'New'},
            {'id': 42, 'name': 'Luke', 'testament': 'New'},
            {'id': 43, 'name': 'John', 'testament': 'New'},
            {'id': 44, 'name': 'Acts', 'testament': 'New'},
            {'id': 45, 'name': 'Romans', 'testament': 'New'},
            {'id': 46, 'name': '1 Corinthians', 'testament': 'New'},
            {'id': 47, 'name': '2 Corinthians', 'testament': 'New'},
            {'id': 48, 'name': 'Galatians', 'testament': 'New'},
            {'id': 49, 'name': 'Ephesians', 'testament': 'New'},
            {'id': 50, 'name': 'Philippians', 'testament': 'New'},
            {'id': 51, 'name': 'Colossians', 'testament': 'New'},
            {'id': 52, 'name': '1 Thessalonians', 'testament': 'New'},
            {'id': 53, 'name': '2 Thessalonians', 'testament': 'New'},
            {'id': 54, 'name': '1 Timothy', 'testament': 'New'},
            {'id': 55, 'name': '2 Timothy', 'testament': 'New'},
            {'id': 56, 'name': 'Titus', 'testament': 'New'},
            {'id': 57, 'name': 'Philemon', 'testament': 'New'},
            {'id': 58, 'name': 'Hebrews', 'testament': 'New'},
            {'id': 59, 'name': 'James', 'testament': 'New'},
            {'id': 60, 'name': '1 Peter', 'testament': 'New'},
            {'id': 61, 'name': '2 Peter', 'testament': 'New'},
            {'id': 62, 'name': '1 John', 'testament': 'New'},
            {'id': 63, 'name': '2 John', 'testament': 'New'},
            {'id': 64, 'name': '3 John', 'testament': 'New'},
            {'id': 65, 'name': 'Jude', 'testament': 'New'},
            {'id': 66, 'name': 'Revelation', 'testament': 'New'},
        ]
    )
    
    # Seed question difficulties
    difficulties = table(
        'question_difficulties',
        column('id', sa.Integer),
        column('name', sa.String),
        column('description', sa.String),
    )
    
    op.bulk_insert(
        difficulties,
        [
            {'id': 1, 'name': 'Easy', 'description': 'Basic knowledge questions suitable for beginners'},
            {'id': 2, 'name': 'Medium', 'description': 'Intermediate level questions requiring good Bible knowledge'},
            {'id': 3, 'name': 'Hard', 'description': 'Advanced questions for those with deep Biblical understanding'},
        ]
    )
    
    # Seed question categories
    categories = table(
        'question_categories',
        column('id', sa.Integer),
        column('name', sa.String),
        column('description', sa.String),
    )
    
    op.bulk_insert(
        categories,
        [
            {'id': 1, 'name': 'People', 'description': 'Questions about Biblical characters'},
            {'id': 2, 'name': 'Places', 'description': 'Questions about geographical locations in the Bible'},
            {'id': 3, 'name': 'Events', 'description': 'Questions about significant events in the Bible'},
            {'id': 4, 'name': 'Teachings', 'description': 'Questions about Biblical teachings and doctrines'},
            {'id': 5, 'name': 'Prophecies', 'description': 'Questions about Biblical prophecies'},
            {'id': 6, 'name': 'Parables', 'description': 'Questions about parables in the Bible'},
            {'id': 7, 'name': 'Miracles', 'description': 'Questions about miracles in the Bible'},
        ]
    )


def downgrade():
    # Remove seeded data
    op.execute("DELETE FROM question_categories")
    op.execute("DELETE FROM question_difficulties")
    op.execute("DELETE FROM bible_books")