"""add pos analytics tables

Revision ID: aa3e4f5g6h7i8
Revises: a3f4g5h6i7j8
Create Date: 2026-05-06

"""
from alembic import op
import sqlalchemy as sa

revision = "aa3e4f5g6h7i8"
down_revision = "a3f4g5h6i7j8"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "pos_upload_batch",
        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("produk_id", sa.Integer(), sa.ForeignKey("master_produk.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("uploaded_by_id", sa.Integer(), sa.ForeignKey("master_user.id", ondelete="SET NULL"), nullable=True),
        sa.Column("tanggal_mulai", sa.Date(), nullable=False),
        sa.Column("tanggal_selesai", sa.Date(), nullable=False),
        sa.Column("total_rows", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("total_volume", sa.Numeric(15, 3), nullable=False, server_default="0"),
        sa.Column("total_nilai", sa.Numeric(15, 2), nullable=False, server_default="0"),
        sa.Column("created_at", sa.DateTime(), nullable=False, server_default=sa.text("now()")),
    )
    op.create_index("ix_pos_upload_batch_spbu", "pos_upload_batch", ["spbu_id"])
    op.create_unique_constraint(
        "uq_pos_upload_batch_period",
        "pos_upload_batch",
        ["spbu_id", "produk_id", "tanggal_mulai", "tanggal_selesai"],
    )

    op.create_table(
        "pos_transaksi_pertamina",
        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("batch_id", sa.Integer(), sa.ForeignKey("pos_upload_batch.id", ondelete="CASCADE"), nullable=False),
        sa.Column("shift_id", sa.Integer(), sa.ForeignKey("master_spbu_shift.id", ondelete="SET NULL"), nullable=True),
        sa.Column("tanggal", sa.Date(), nullable=False),
        sa.Column("jam", sa.Time(), nullable=False),
        sa.Column("code_mor", sa.String(20), nullable=True),
        sa.Column("province", sa.String(100), nullable=True),
        sa.Column("city", sa.String(100), nullable=True),
        sa.Column("no_spbu_pertamina", sa.String(20), nullable=True),
        sa.Column("no_nozzle", sa.String(10), nullable=True),
        sa.Column("no_dispenser", sa.String(100), nullable=False),
        sa.Column("produk_raw", sa.String(50), nullable=False),
        sa.Column("volume", sa.Numeric(15, 3), nullable=False, server_default="0"),
        sa.Column("nilai", sa.Numeric(15, 2), nullable=False, server_default="0"),
        sa.Column("operator_name", sa.String(100), nullable=True),
        sa.Column("no_hp", sa.String(20), nullable=True),
        sa.Column("delivery_type", sa.String(50), nullable=True),
        sa.Column("plat_nomor", sa.String(20), nullable=True),
        sa.Column("nik", sa.String(30), nullable=True),
        sa.Column("instansi", sa.String(100), nullable=True),
        sa.Column("timestamp", sa.DateTime(timezone=True), nullable=False),
        sa.Column("island_raw", sa.String(50), nullable=True),
        sa.Column("nozzle_raw", sa.String(20), nullable=True),
    )
    op.create_index("ix_pos_transaksi_spbu", "pos_transaksi_pertamina", ["spbu_id"])
    op.create_index("ix_pos_transaksi_batch", "pos_transaksi_pertamina", ["batch_id"])
    op.create_index("ix_pos_transaksi_plat", "pos_transaksi_pertamina", ["plat_nomor"])
    op.create_index("ix_pos_transaksi_tanggal", "pos_transaksi_pertamina", ["tanggal"])
    op.create_index("ix_pos_transaksi_shift", "pos_transaksi_pertamina", ["shift_id"])
    op.create_unique_constraint(
        "uq_pos_transaksi_dedup",
        "pos_transaksi_pertamina",
        ["spbu_id", "timestamp", "no_dispenser"],
    )


def downgrade() -> None:
    op.drop_table("pos_transaksi_pertamina")
    op.drop_table("pos_upload_batch")