"""add pb module

Revision ID: dc6e7f8a9b0c
Revises: cb5d6e7f8a9b
Create Date: 2026-04-24

"""
from alembic import op
import sqlalchemy as sa

revision = "dc6e7f8a9b0c"
down_revision = "cb5d6e7f8a9b"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # pb_project
    op.create_table(
        "pb_project",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("spbu_id", sa.Integer, sa.ForeignKey("master_spbu.id", ondelete="CASCADE"), nullable=False),
        sa.Column("nama", sa.String(200), nullable=False),
        sa.Column("deskripsi", sa.Text, nullable=True),
        sa.Column("is_closed", sa.Boolean, nullable=False, server_default="false"),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
    )
    op.create_index("ix_pb_project_spbu_id", "pb_project", ["spbu_id"])

    # pb
    op.create_table(
        "pb",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("spbu_id", sa.Integer, sa.ForeignKey("master_spbu.id", ondelete="CASCADE"), nullable=False),
        sa.Column("project_id", sa.Integer, sa.ForeignKey("pb_project.id", ondelete="SET NULL"), nullable=True),
        sa.Column("nomor", sa.String(30), nullable=False, unique=True),
        sa.Column("judul", sa.String(300), nullable=False),
        sa.Column("deskripsi", sa.Text, nullable=True),
        sa.Column("is_kas_kecil", sa.Boolean, nullable=False, server_default="false"),
        sa.Column("status", sa.String(20), nullable=False, server_default="draft"),
        sa.Column("attachment_url", sa.String(500), nullable=True),
        sa.Column("created_by_id", sa.Integer, sa.ForeignKey("master_user.id", ondelete="SET NULL"), nullable=True),
        sa.Column("submitted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("forwarded_by_id", sa.Integer, sa.ForeignKey("master_user.id", ondelete="SET NULL"), nullable=True),
        sa.Column("forwarded_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("recalled_by_id", sa.Integer, sa.ForeignKey("master_user.id", ondelete="SET NULL"), nullable=True),
        sa.Column("recalled_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("reviewed_by_id", sa.Integer, sa.ForeignKey("master_user.id", ondelete="SET NULL"), nullable=True),
        sa.Column("reviewed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("catatan_head", sa.Text, nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
    )
    op.create_index("ix_pb_spbu_id", "pb", ["spbu_id"])
    op.create_index("ix_pb_project_id", "pb", ["project_id"])
    op.create_index("ix_pb_status", "pb", ["status"])

    # pb_item
    op.create_table(
        "pb_item",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("pb_id", sa.Integer, sa.ForeignKey("pb.id", ondelete="CASCADE"), nullable=False),
        sa.Column("urutan", sa.Integer, nullable=False, server_default="0"),
        sa.Column("nama_item", sa.String(300), nullable=False),
        sa.Column("qty_requested", sa.Numeric(15, 3), nullable=False),
        sa.Column("satuan", sa.String(50), nullable=False),
        sa.Column("harga_satuan", sa.Numeric(15, 2), nullable=False),
        sa.Column("status", sa.String(20), nullable=False, server_default="pending"),
        sa.Column("qty_approved", sa.Numeric(15, 3), nullable=True),
        sa.Column("catatan_head", sa.Text, nullable=True),
    )
    op.create_index("ix_pb_item_pb_id", "pb_item", ["pb_id"])

    # Add pb_id to expenses
    op.add_column("expenses", sa.Column("pb_id", sa.Integer, sa.ForeignKey("pb.id", ondelete="SET NULL"), nullable=True))
    op.create_index("ix_expenses_pb_id", "expenses", ["pb_id"])

    # Add pb to modulenum enum (PostgreSQL native enum)
    op.execute("ALTER TYPE modulenum ADD VALUE IF NOT EXISTS 'pb'")


def downgrade() -> None:
    op.drop_index("ix_expenses_pb_id", "expenses")
    op.drop_column("expenses", "pb_id")
    op.drop_table("pb_item")
    op.drop_table("pb")
    op.drop_table("pb_project")
    # Note: cannot remove enum value in PostgreSQL without dropping/recreating the type