"""001_initial_schema

Revision ID: 001_initial_schema
Revises: 
Create Date: 2025-11-02

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

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


def upgrade() -> None:
    # Enable UUID extension
    op.execute('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
    
    # Create users table
    op.create_table(
        'users',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True, server_default=sa.text('uuid_generate_v4()')),
        sa.Column('email', sa.String(255), nullable=False, unique=True),
        sa.Column('password_hash', sa.String(255), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.Column('updated_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default=sa.text('TRUE')),
        sa.CheckConstraint('email = LOWER(email)', name='check_email_lowercase')
    )
    op.create_index('idx_users_created_at', 'users', ['created_at'])
    op.create_index('idx_users_email', 'users', ['email'], unique=True)
    
    # Create boards table
    op.create_table(
        'boards',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True, server_default=sa.text('uuid_generate_v4()')),
        sa.Column('user_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('users.id', ondelete='CASCADE'), nullable=False),
        sa.Column('title', sa.String(255), nullable=False),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('viewport_state', postgresql.JSONB(), nullable=False, server_default=sa.text("'{\"x\": 0, \"y\": 0, \"zoom\": 1.0, \"rotation\": 0}'::jsonb")),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.Column('updated_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.Column('is_deleted', sa.Boolean(), nullable=False, server_default=sa.text('FALSE')),
        sa.CheckConstraint('LENGTH(title) > 0', name='check_title_not_empty')
    )
    op.create_index('idx_boards_user_created', 'boards', ['user_id', 'created_at'])
    op.create_index('idx_boards_updated', 'boards', ['updated_at'])
    op.execute('CREATE INDEX idx_boards_viewport ON boards USING GIN (viewport_state)')
    
    # Create images table
    op.create_table(
        'images',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True, server_default=sa.text('uuid_generate_v4()')),
        sa.Column('user_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('users.id', ondelete='CASCADE'), nullable=False),
        sa.Column('filename', sa.String(255), nullable=False),
        sa.Column('storage_path', sa.String(512), nullable=False),
        sa.Column('file_size', sa.BigInteger(), nullable=False),
        sa.Column('mime_type', sa.String(100), nullable=False),
        sa.Column('width', sa.Integer(), nullable=False),
        sa.Column('height', sa.Integer(), nullable=False),
        sa.Column('image_metadata', postgresql.JSONB(), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.Column('reference_count', sa.Integer(), nullable=False, server_default=sa.text('0')),
        sa.CheckConstraint('file_size > 0 AND file_size <= 52428800', name='check_file_size'),
        sa.CheckConstraint('width > 0 AND width <= 10000', name='check_width'),
        sa.CheckConstraint('height > 0 AND height <= 10000', name='check_height')
    )
    op.create_index('idx_images_user_created', 'images', ['user_id', 'created_at'])
    op.create_index('idx_images_filename', 'images', ['filename'])
    op.execute('CREATE INDEX idx_images_metadata ON images USING GIN (image_metadata)')
    
    # Create groups table
    op.create_table(
        'groups',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True, server_default=sa.text('uuid_generate_v4()')),
        sa.Column('board_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('boards.id', ondelete='CASCADE'), nullable=False),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('color', sa.String(7), nullable=False),
        sa.Column('annotation', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.Column('updated_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.CheckConstraint('LENGTH(name) > 0', name='check_name_not_empty'),
        sa.CheckConstraint("color ~ '^#[0-9A-Fa-f]{6}$'", name='check_color_hex')
    )
    op.create_index('idx_groups_board_created', 'groups', ['board_id', 'created_at'])
    
    # Create board_images table
    op.create_table(
        'board_images',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True, server_default=sa.text('uuid_generate_v4()')),
        sa.Column('board_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('boards.id', ondelete='CASCADE'), nullable=False),
        sa.Column('image_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('images.id', ondelete='CASCADE'), nullable=False),
        sa.Column('position', postgresql.JSONB(), nullable=False),
        sa.Column('transformations', postgresql.JSONB(), nullable=False, server_default=sa.text("'{\"scale\": 1.0, \"rotation\": 0, \"opacity\": 1.0, \"flipped_h\": false, \"flipped_v\": false, \"greyscale\": false}'::jsonb")),
        sa.Column('z_order', sa.Integer(), nullable=False, server_default=sa.text('0')),
        sa.Column('group_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('groups.id', ondelete='SET NULL'), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.Column('updated_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()'))
    )
    op.create_unique_constraint('uq_board_image', 'board_images', ['board_id', 'image_id'])
    op.create_index('idx_board_images_board_z', 'board_images', ['board_id', 'z_order'])
    op.create_index('idx_board_images_group', 'board_images', ['group_id'])
    op.execute('CREATE INDEX idx_board_images_position ON board_images USING GIN (position)')
    op.execute('CREATE INDEX idx_board_images_transformations ON board_images USING GIN (transformations)')
    
    # Create share_links table
    op.create_table(
        'share_links',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True, server_default=sa.text('uuid_generate_v4()')),
        sa.Column('board_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('boards.id', ondelete='CASCADE'), nullable=False),
        sa.Column('token', sa.String(64), nullable=False, unique=True),
        sa.Column('permission_level', sa.String(20), nullable=False),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.Column('expires_at', sa.DateTime(), nullable=True),
        sa.Column('last_accessed_at', sa.DateTime(), nullable=True),
        sa.Column('access_count', sa.Integer(), nullable=False, server_default=sa.text('0')),
        sa.Column('is_revoked', sa.Boolean(), nullable=False, server_default=sa.text('FALSE')),
        sa.CheckConstraint("permission_level IN ('view-only', 'view-comment')", name='check_permission_level')
    )
    op.create_unique_constraint('uq_share_links_token', 'share_links', ['token'])
    op.create_index('idx_share_links_board_revoked', 'share_links', ['board_id', 'is_revoked'])
    op.create_index('idx_share_links_expires_revoked', 'share_links', ['expires_at', 'is_revoked'])
    
    # Create comments table
    op.create_table(
        'comments',
        sa.Column('id', postgresql.UUID(as_uuid=True), primary_key=True, server_default=sa.text('uuid_generate_v4()')),
        sa.Column('board_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('boards.id', ondelete='CASCADE'), nullable=False),
        sa.Column('share_link_id', postgresql.UUID(as_uuid=True), sa.ForeignKey('share_links.id', ondelete='SET NULL'), nullable=True),
        sa.Column('author_name', sa.String(100), nullable=False),
        sa.Column('content', sa.Text(), nullable=False),
        sa.Column('position', postgresql.JSONB(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False, server_default=sa.text('NOW()')),
        sa.Column('is_deleted', sa.Boolean(), nullable=False, server_default=sa.text('FALSE')),
        sa.CheckConstraint('LENGTH(content) > 0 AND LENGTH(content) <= 5000', name='check_content_length')
    )
    op.create_index('idx_comments_board_created', 'comments', ['board_id', 'created_at'])
    op.create_index('idx_comments_share_link', 'comments', ['share_link_id'])
    
    # Create triggers for updated_at
    op.execute("""
        CREATE OR REPLACE FUNCTION update_updated_at_column()
        RETURNS TRIGGER AS $$
        BEGIN
            NEW.updated_at = NOW();
            RETURN NEW;
        END;
        $$ language 'plpgsql';
    """)
    
    op.execute('CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()')
    op.execute('CREATE TRIGGER update_boards_updated_at BEFORE UPDATE ON boards FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()')
    op.execute('CREATE TRIGGER update_groups_updated_at BEFORE UPDATE ON groups FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()')
    op.execute('CREATE TRIGGER update_board_images_updated_at BEFORE UPDATE ON board_images FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()')


def downgrade() -> None:
    # Drop triggers
    op.execute('DROP TRIGGER IF EXISTS update_board_images_updated_at ON board_images')
    op.execute('DROP TRIGGER IF EXISTS update_groups_updated_at ON groups')
    op.execute('DROP TRIGGER IF EXISTS update_boards_updated_at ON boards')
    op.execute('DROP TRIGGER IF EXISTS update_users_updated_at ON users')
    op.execute('DROP FUNCTION IF EXISTS update_updated_at_column()')
    
    # Drop tables in reverse order
    op.drop_table('comments')
    op.drop_table('share_links')
    op.drop_table('board_images')
    op.drop_table('groups')
    op.drop_table('images')
    op.drop_table('boards')
    op.drop_table('users')
    
    # Drop extension
    op.execute('DROP EXTENSION IF EXISTS "uuid-ossp"')