pmdi/backend/alembic/versions/0001_initial.py

"""Initial schema — create all PMDI tables.

Revision ID: 0001_initial
Revises: 
Create Date: 2026-08-31 00:00:00.000000

Creates:
  · admin_users
  · clients
  · email_templates
  · invoices            (with PostgreSQL ARRAY(Integer) for notification_dates)
  · notification_logs   (with JSONB context column)
"""
from __future__ import annotations

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

# revision identifiers
revision: str           = "0001_initial"
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # ── admin_users ────────────────────────────────────────────────────────
    op.create_table(
        "admin_users",
        sa.Column("id",              sa.Integer(),     nullable=False),
        sa.Column("username",        sa.String(64),    nullable=False),
        sa.Column("hashed_password", sa.String(256),   nullable=False),
        sa.Column("totp_secret",     sa.String(64),    nullable=True),
        sa.Column("mfa_enabled",     sa.Boolean(),     nullable=False, server_default="false"),
        sa.Column("created_at",      sa.DateTime(),    nullable=False, server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("username"),
    )
    op.create_index("ix_admin_users_id",       "admin_users", ["id"],       unique=False)
    op.create_index("ix_admin_users_username", "admin_users", ["username"], unique=True)

    # ── clients ────────────────────────────────────────────────────────────
    op.create_table(
        "clients",
        sa.Column("id",             sa.Integer(),     nullable=False),
        sa.Column("client_id_name", sa.String(64),    nullable=False),
        sa.Column("client_name",    sa.String(256),   nullable=False),
        sa.Column("client_email",   sa.String(256),   nullable=False),
        sa.Column("created_at",     sa.DateTime(),    nullable=True,  server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("client_id_name"),
    )
    op.create_index("ix_clients_id", "clients", ["id"], unique=False)

    # ── email_templates ────────────────────────────────────────────────────
    op.create_table(
        "email_templates",
        sa.Column("id",            sa.Integer(),     nullable=False),
        sa.Column("name",          sa.String(128),   nullable=False),
        sa.Column("template_type", sa.String(32),    nullable=False),
        sa.Column("subject",       sa.String(256),   nullable=False),
        sa.Column("template_body", sa.Text(),        nullable=False),
        sa.Column("created_at",    sa.DateTime(),    nullable=True,  server_default=sa.func.now()),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_email_templates_id", "email_templates", ["id"], unique=False)

    # ── invoices ───────────────────────────────────────────────────────────
    op.create_table(
        "invoices",
        sa.Column("id",                     sa.Integer(),  nullable=False),
        sa.Column("invoice_number",         sa.String(64), nullable=False),
        sa.Column("client_id",              sa.Integer(),  nullable=False),
        sa.Column("pay_date",               sa.Date(),     nullable=False),
        sa.Column("template_id",            sa.Integer(),  nullable=False),
        sa.Column("notification_dates",     postgresql.ARRAY(sa.Integer()), nullable=True),
        sa.Column("overdue_recurring_days", sa.Integer(),  nullable=False, server_default="0"),
        sa.Column("paid",                   sa.Boolean(),  nullable=False, server_default="false"),
        sa.Column("created_at",             sa.DateTime(), nullable=True,  server_default=sa.func.now()),
        sa.ForeignKeyConstraint(["client_id"],   ["clients.id"],         ondelete="RESTRICT"),
        sa.ForeignKeyConstraint(["template_id"], ["email_templates.id"], ondelete="RESTRICT"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("invoice_number"),
    )
    op.create_index("ix_invoices_id", "invoices", ["id"], unique=False)

    # ── notification_logs ──────────────────────────────────────────────────
    op.create_table(
        "notification_logs",
        sa.Column("id",            sa.Integer(),          nullable=False),
        sa.Column("invoice_id",    sa.Integer(),          nullable=False),
        sa.Column("status",        sa.String(64),         nullable=False),
        sa.Column("sent_at",       sa.DateTime(),         nullable=False, server_default=sa.func.now()),
        sa.Column("context",       postgresql.JSONB(),    nullable=True),
        sa.Column("error_message", sa.Text(),             nullable=True),
        sa.ForeignKeyConstraint(["invoice_id"], ["invoices.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_notification_logs_id", "notification_logs", ["id"], unique=False)


def downgrade() -> None:
    op.drop_index("ix_notification_logs_id", table_name="notification_logs")
    op.drop_table("notification_logs")
    op.drop_index("ix_invoices_id", table_name="invoices")
    op.drop_table("invoices")
    op.drop_index("ix_email_templates_id", table_name="email_templates")
    op.drop_table("email_templates")
    op.drop_index("ix_clients_id", table_name="clients")
    op.drop_table("clients")
    op.drop_index("ix_admin_users_username", table_name="admin_users")
    op.drop_index("ix_admin_users_id", table_name="admin_users")
    op.drop_table("admin_users")