"""Add expense_kategori and expenses tables.

Revision ID: k6f7a8b9c0d1
Revises: j5e6f7a8b9c0
Create Date: 2026-04-03
"""

from alembic import op
import sqlalchemy as sa

revision = 'k6f7a8b9c0d1'
down_revision = 'j5e6f7a8b9c0'
branch_labels = None
depends_on = None

KATEGORI_DEFAULT = [
    'Gaji Karyawan', 'ATK & Administrasi', 'Inventaris', 'Biaya Listrik',
    'Wifi', 'Maintenance', 'Transport', 'Konsumsi AMT',
    'Keamanan', 'Ops Angkutan', 'PPH', 'Biaya Bank', 'Lain-lain', 'BPJS',
    'Insentif Pasti Pas',
]


def upgrade() -> None:
    op.create_table(
        'expense_kategori',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('spbu_id', sa.Integer(), sa.ForeignKey('master_spbu.id', ondelete='CASCADE'), nullable=True),
        sa.Column('nama', sa.String(100), nullable=False),
        sa.Column('urutan', sa.Integer(), nullable=False, server_default='0'),
        sa.Column('is_active', sa.Boolean(), nullable=False, server_default='true'),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_expense_kategori_spbu_id', 'expense_kategori', ['spbu_id'])

    # Seed global default categories
    op.bulk_insert(
        sa.table('expense_kategori',
            sa.column('spbu_id', sa.Integer),
            sa.column('nama', sa.String),
            sa.column('urutan', sa.Integer),
            sa.column('is_active', sa.Boolean),
        ),
        [{'spbu_id': None, 'nama': nama, 'urutan': i + 1, 'is_active': True}
         for i, nama in enumerate(KATEGORI_DEFAULT)]
    )

    op.create_table(
        'expenses',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('spbu_id', sa.Integer(), sa.ForeignKey('master_spbu.id', ondelete='CASCADE'), nullable=False),
        sa.Column('laporan_shift_id', sa.Integer(), sa.ForeignKey('laporan_shift.id', ondelete='CASCADE'), nullable=True),
        sa.Column('tanggal', sa.Date(), nullable=False),
        sa.Column('kategori_id', sa.Integer(), sa.ForeignKey('expense_kategori.id', ondelete='RESTRICT'), nullable=False),
        sa.Column('keterangan', sa.Text(), nullable=True),
        sa.Column('jumlah', sa.Numeric(15, 2), nullable=False),
        sa.Column('bukti_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('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
        sa.PrimaryKeyConstraint('id'),
    )
    op.create_index('ix_expenses_spbu_id_tanggal', 'expenses', ['spbu_id', 'tanggal'])
    op.create_index('ix_expenses_laporan_shift_id', 'expenses', ['laporan_shift_id'])


def downgrade() -> None:
    op.drop_index('ix_expenses_laporan_shift_id', table_name='expenses')
    op.drop_index('ix_expenses_spbu_id_tanggal', table_name='expenses')
    op.drop_table('expenses')
    op.drop_index('ix_expense_kategori_spbu_id', table_name='expense_kategori')
    op.drop_table('expense_kategori')