"""last_sync table — tracks per-table DSE data sync timestamps

Revision ID: f7ad4090e7ba
Revises: c1f8a2d3e5b7
Create Date: 2026-06-03

Tracks the last successfully synced data timestamp for each IMDS source table.
The DseRetrieve service reads this to know where to resume incremental fetching.

Rows seeded:  TRD | IDX | MAN | MKISTAT
"""

from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision: str = "f7ad4090e7ba"
down_revision: str = "c1f8a2d3e5b7"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "last_sync",
        sa.Column("id",                   sa.Integer,  primary_key=True, autoincrement=True),
        sa.Column("table_name",           sa.String(20), nullable=False, unique=True,
                  comment="Source table name: TRD | IDX | MAN | MKISTAT"),
        sa.Column("last_synced_timestamp", sa.DateTime, nullable=True,
                  comment="Latest data timestamp fetched from DSE source"),
        sa.Column(
            "last_synced_at",
            sa.DateTime,
            nullable=True,
            server_default=sa.text("NOW()"),
            comment="Wall-clock time this row was last updated",
        ),
    )

    op.create_index("ix_last_sync_table_name", "last_sync", ["table_name"], unique=True)

    # Seed initial rows — start from epoch so first run fetches everything.
    op.execute(
        """
        INSERT INTO last_sync (table_name, last_synced_timestamp) VALUES
            ('TRD',     '2000-01-01 00:00:00'),
            ('IDX',     '2000-01-01 00:00:00'),
            ('MAN',     '2000-01-01 00:00:00'),
            ('MKISTAT', '2000-01-01 00:00:00')
        """
    )


def downgrade() -> None:
    op.drop_index("ix_last_sync_table_name", table_name="last_sync")
    op.drop_table("last_sync")