"""mkistat extended columns and mkt_security_code table

Revision ID: 29d799b3e124
Revises: f7ad4090e7ba
Create Date: 2026-06-03

Changes:
  imds_mkistat_data:
    + MKISTAT_SECTOR              VARCHAR(100)  — sector derived from mkt_security_code
    + MKISTAT_YESTERDAY_CHANGE_PCT DOUBLE PRECISION — calculated pct change vs yday close

  mkt_security_code (new table):
    Lookup table mapping instrument_code → sector.
    Populated by ops team or a separate seeder; service_1 reads it at startup
    (TTL-cached for 8 h) to enrich MKISTAT rows before insert.
"""

from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision: str = "29d799b3e124"
down_revision: str = "f7ad4090e7ba"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # -----------------------------------------------------------------------
    # 1. Add two derived columns to imds_mkistat_data
    # -----------------------------------------------------------------------
    op.add_column(
        "imds_mkistat_data",
        sa.Column(
            "MKISTAT_SECTOR",
            sa.String(100),
            nullable=True,
            comment="Sector derived from mkt_security_code lookup",
        ),
    )
    op.add_column(
        "imds_mkistat_data",
        sa.Column(
            "MKISTAT_YESTERDAY_CHANGE_PCT",
            sa.Float,
            nullable=True,
            comment="Pct change: (last - yday_close) / yday_close * 100",
        ),
    )

    op.create_index(
        "ix_imds_mkistat_sector",
        "imds_mkistat_data",
        ["MKISTAT_SECTOR"],
    )

    # -----------------------------------------------------------------------
    # 2. Create mkt_security_code lookup table
    # -----------------------------------------------------------------------
    op.create_table(
        "mkt_security_code",
        sa.Column("id",            sa.Integer,    primary_key=True, autoincrement=True),
        sa.Column("security_code", sa.String(50),  nullable=False, unique=True,
                  comment="Matches MKISTAT_INSTRUMENT_CODE from DSE feed"),
        sa.Column("sector",        sa.String(100), nullable=True,
                  comment="Exchange sector / industry classification"),
        sa.Column(
            "created_at",
            sa.DateTime,
            nullable=True,
            server_default=sa.text("NOW()"),
        ),
        sa.Column(
            "updated_at",
            sa.DateTime,
            nullable=True,
            server_default=sa.text("NOW()"),
        ),
    )
    op.create_index(
        "ix_mkt_security_code_code", "mkt_security_code", ["security_code"], unique=True
    )


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

    op.drop_index("ix_imds_mkistat_sector",   table_name="imds_mkistat_data")
    op.drop_column("imds_mkistat_data", "MKISTAT_YESTERDAY_CHANGE_PCT")
    op.drop_column("imds_mkistat_data", "MKISTAT_SECTOR")