"""add payroll

Revision ID: d1e2f3a4b5c6
Revises: c0d1e2f3a4b5
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "d1e2f3a4b5c6"
down_revision = "c0d1e2f3a4b5"
branch_labels = None
depends_on = None

def upgrade():
    op.create_table("payroll_profiles",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column("business_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("businesses.id", ondelete="CASCADE"), nullable=False),
        sa.Column("membership_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("memberships.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("salary", sa.Numeric(14,2), nullable=False), sa.Column("payment_frequency", sa.String(20), nullable=False), sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.CheckConstraint("salary >= 0", name="ck_payroll_profile_salary_nonnegative"), sa.CheckConstraint("payment_frequency IN ('MONTHLY', 'WEEKLY', 'DAILY', 'OTHER')", name="ck_payroll_profile_frequency"))
    op.create_index("ix_payroll_profiles_business_id", "payroll_profiles", ["business_id"])
    op.create_index("ix_payroll_profiles_membership_id", "payroll_profiles", ["membership_id"])
    op.create_index("uq_payroll_profile_active_membership", "payroll_profiles", ["business_id", "membership_id"], unique=True, postgresql_where=sa.text("is_active"))
    op.create_table("payroll_runs",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True), sa.Column("business_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("businesses.id", ondelete="CASCADE"), nullable=False), sa.Column("membership_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("memberships.id", ondelete="RESTRICT"), nullable=False), sa.Column("payroll_profile_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("payroll_profiles.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("period_start", sa.Date(), nullable=False), sa.Column("period_end", sa.Date(), nullable=False), sa.Column("base_salary", sa.Numeric(14,2), nullable=False), sa.Column("adjustment_amount", sa.Numeric(14,2), nullable=False, server_default="0"), sa.Column("adjustment_note", sa.Text()), sa.Column("total_amount", sa.Numeric(14,2), nullable=False), sa.Column("payment_method", sa.String(20)), sa.Column("status", sa.String(20), nullable=False, server_default="DRAFT"),
        sa.Column("approved_by", postgresql.UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="RESTRICT")), sa.Column("approved_at", sa.DateTime(timezone=True)), sa.Column("paid_by", postgresql.UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="RESTRICT")), sa.Column("paid_at", sa.DateTime(timezone=True)), sa.Column("expense_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("expenses.id", ondelete="RESTRICT"), unique=True), sa.Column("created_by", postgresql.UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="RESTRICT"), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
        sa.CheckConstraint("period_end >= period_start", name="ck_payroll_run_period_order"), sa.CheckConstraint("base_salary >= 0", name="ck_payroll_run_base_nonnegative"), sa.CheckConstraint("total_amount >= 0", name="ck_payroll_run_total_nonnegative"), sa.CheckConstraint("status IN ('DRAFT', 'APPROVED', 'PAID', 'CANCELLED')", name="ck_payroll_run_status"), sa.CheckConstraint("payment_method IS NULL OR payment_method IN ('CASH', 'POS', 'TRANSFER', 'OTHER')", name="ck_payroll_run_payment_method"))
    op.create_index("ix_payroll_runs_business_id", "payroll_runs", ["business_id"]); op.create_index("ix_payroll_runs_membership_id", "payroll_runs", ["membership_id"]); op.create_index("ix_payroll_runs_status", "payroll_runs", ["status"])
    op.create_index("uq_payroll_run_active_period", "payroll_runs", ["business_id", "membership_id", "period_start", "period_end"], unique=True, postgresql_where=sa.text("status <> 'CANCELLED'"))
def downgrade():
    op.drop_table("payroll_runs"); op.drop_table("payroll_profiles")