"""add task board tables (folders, items, subitems)

Revision ID: b6f3a1d94c27
Revises: 7c2d9e4b1a36
Create Date: 2026-09-27 10:00:00.000000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'b6f3a1d94c27'
down_revision = '7c2d9e4b1a36'
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        'task_folders',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=120), nullable=False),
        sa.Column('color', sa.String(length=20), nullable=False, server_default='gold'),
        sa.Column('is_archived', sa.Boolean(), nullable=False, server_default=sa.text('0')),
        sa.Column('created_by', sa.Integer(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.ForeignKeyConstraint(['created_by'], ['users.id'], ),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('name', name='uq_task_folder_name'),
    )

    op.create_table(
        'task_items',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('folder_id', sa.Integer(), nullable=False),
        sa.Column('title', sa.String(length=300), nullable=False),
        sa.Column('customer_id', sa.Integer(), nullable=True),
        sa.Column('assignee_id', sa.Integer(), nullable=True),
        sa.Column('due_date', sa.Date(), nullable=True),
        sa.Column('due_time', sa.Time(), nullable=True),
        sa.Column('priority', sa.Integer(), nullable=False, server_default=sa.text('0')),
        sa.Column('repeat_rule', sa.String(length=20), nullable=True),
        sa.Column('notes', sa.Text(), nullable=True),
        sa.Column('is_done', sa.Boolean(), nullable=False, server_default=sa.text('0')),
        sa.Column('done_at', sa.DateTime(), nullable=True),
        sa.Column('done_by', sa.Integer(), nullable=True),
        sa.Column('created_by', sa.Integer(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False),
        sa.ForeignKeyConstraint(['assignee_id'], ['users.id'], ),
        sa.ForeignKeyConstraint(['created_by'], ['users.id'], ),
        sa.ForeignKeyConstraint(['customer_id'], ['customers.id'], ondelete='SET NULL'),
        sa.ForeignKeyConstraint(['done_by'], ['users.id'], ),
        sa.ForeignKeyConstraint(['folder_id'], ['task_folders.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
    )
    with op.batch_alter_table('task_items', schema=None) as batch_op:
        batch_op.create_index('idx_task_items_folder', ['folder_id', 'is_done'], unique=False)
        batch_op.create_index('idx_task_items_due', ['is_done', 'due_date'], unique=False)

    op.create_table(
        'task_subitems',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('task_id', sa.Integer(), nullable=False),
        sa.Column('title', sa.String(length=300), nullable=False),
        sa.Column('is_done', sa.Boolean(), nullable=False, server_default=sa.text('0')),
        sa.ForeignKeyConstraint(['task_id'], ['task_items.id'], ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('id'),
    )
    with op.batch_alter_table('task_subitems', schema=None) as batch_op:
        batch_op.create_index('idx_task_subitems_task', ['task_id'], unique=False)


def downgrade():
    with op.batch_alter_table('task_subitems', schema=None) as batch_op:
        batch_op.drop_index('idx_task_subitems_task')
    op.drop_table('task_subitems')

    with op.batch_alter_table('task_items', schema=None) as batch_op:
        batch_op.drop_index('idx_task_items_due')
        batch_op.drop_index('idx_task_items_folder')
    op.drop_table('task_items')

    op.drop_table('task_folders')