"""Add society projects tables

Revision ID: 006_add_society_projects
Revises: 005_add_geo_location
Create Date: 2025-03-07

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '006_add_society_projects'
down_revision = '005_add_geo_location'
branch_labels = None
depends_on = None


def upgrade():
    # Society Projects table
    op.create_table('society_projects',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), nullable=False),
        sa.Column('title', sa.String(500), nullable=False),
        sa.Column('title_ar', sa.String(500)),
        sa.Column('slug', sa.String(255), nullable=False, unique=True),
        sa.Column('description', sa.Text()),
        sa.Column('description_ar', sa.Text()),
        sa.Column('content', sa.Text()),
        sa.Column('video_url', sa.String(500)),
        sa.Column('video_path', sa.String(500)),
        sa.Column('pdf_path', sa.String(500)),
        sa.Column('cover_image', sa.String(500)),
        sa.Column('notebooklm_data', sa.JSON()),
        sa.Column('status', sa.String(20), default='pending'),
        sa.Column('is_featured', sa.Boolean(), default=False),
        sa.Column('view_count', sa.Integer(), default=0),
        sa.Column('sponsorship_tiers', sa.JSON()),
        sa.Column('sponsorship_target', sa.Integer()),
        sa.Column('currency', sa.String(10), default='SAR'),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
        sa.Column('updated_at', sa.DateTime(), default=sa.func.utcnow(), onupdate=sa.func.utcnow()),
        sa.Column('published_at', sa.DateTime()),
    )
    op.create_index('idx_society_projects_slug', 'society_projects', ['slug'])
    op.create_index('idx_society_projects_status', 'society_projects', ['status'])
    op.create_index('idx_society_projects_user', 'society_projects', ['user_id'])
    op.create_index('idx_society_projects_featured', 'society_projects', ['is_featured'])

    # Society Project Sponsors table
    op.create_table('society_project_sponsors',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('project_id', sa.Integer(), sa.ForeignKey('society_projects.id'), nullable=False),
        sa.Column('sponsor_id', sa.Integer(), sa.ForeignKey('users.id'), nullable=False),
        sa.Column('sponsor_type', sa.String(50), default='individual'),
        sa.Column('tier', sa.String(20), default='bronze'),
        sa.Column('logo_url', sa.String(500)),
        sa.Column('website_url', sa.String(500)),
        sa.Column('status', sa.String(20), default='pending'),
        sa.Column('monthly_contribution', sa.Numeric(10, 2)),
        sa.Column('notes', sa.Text()),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
        sa.Column('approved_at', sa.DateTime()),
    )
    op.create_index('idx_society_sponsors_project', 'society_project_sponsors', ['project_id'])
    op.create_index('idx_society_sponsors_sponsor', 'society_project_sponsors', ['sponsor_id'])
    op.create_index('idx_society_sponsors_status', 'society_project_sponsors', ['status'])

    # Society Project Likes table
    op.create_table('society_project_likes',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('project_id', sa.Integer(), sa.ForeignKey('society_projects.id'), nullable=False),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), nullable=False),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
        sa.UniqueConstraint('project_id', 'user_id', name='uq_society_project_like'),
    )
    op.create_index('idx_society_likes_project', 'society_project_likes', ['project_id'])
    op.create_index('idx_society_likes_user', 'society_project_likes', ['user_id'])

    # Society Project Comments table
    op.create_table('society_project_comments',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('project_id', sa.Integer(), sa.ForeignKey('society_projects.id'), nullable=False),
        sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), nullable=False),
        sa.Column('parent_id', sa.Integer(), sa.ForeignKey('society_project_comments.id')),
        sa.Column('content', sa.Text(), nullable=False),
        sa.Column('is_deleted', sa.Boolean(), default=False),
        sa.Column('created_at', sa.DateTime(), default=sa.func.utcnow()),
        sa.Column('updated_at', sa.DateTime(), default=sa.func.utcnow(), onupdate=sa.func.utcnow()),
    )
    op.create_index('idx_society_comments_project', 'society_project_comments', ['project_id'])
    op.create_index('idx_society_comments_user', 'society_project_comments', ['user_id'])
    op.create_index('idx_society_comments_parent', 'society_project_comments', ['parent_id'])


def downgrade():
    op.drop_table('society_project_comments')
    op.drop_table('society_project_likes')
    op.drop_table('society_project_sponsors')
    op.drop_table('society_projects')