"""initial_schema

Revision ID: 52abc790351d
Revises: 
Create Date: 2026-01-18 08:34:50.268181

"""
from typing import Sequence, Union

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

# revision identifiers, used by Alembic.
revision: str = '52abc790351d'
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:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('admin_audit_log',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('ts', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('actor_user_hash', sa.Text(), nullable=False),
    sa.Column('target_user_hash', sa.Text(), nullable=False),
    sa.Column('target_username_hash', sa.Text(), nullable=True),
    sa.Column('action', sa.Text(), nullable=False),
    sa.Column('meta', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('admin_role_audit',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('actor_user_id', sa.String(), nullable=False),
    sa.Column('target_user_id', sa.String(), nullable=False),
    sa.Column('old_role', sa.String(), nullable=False),
    sa.Column('new_role', sa.String(), nullable=False),
    sa.Column('changed_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_table('app_user',
    sa.Column('id', sa.String(), nullable=False),
    sa.Column('username', sa.String(), nullable=False),
    sa.Column('password_hash', sa.String(), nullable=False),
    sa.Column('is_admin', sa.Boolean(), server_default=sa.text('false'), nullable=False),
    sa.Column('is_super_admin', sa.Boolean(), server_default=sa.text('false'), nullable=False),
    sa.Column('role', sa.String(), server_default=sa.text("'USER'"), nullable=False),
    sa.CheckConstraint("role IN ('USER','ADMIN','SUPER_ADMIN')", name='chk_app_user_role'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('username')
    )
    op.create_index('idx_app_user_is_admin', 'app_user', ['is_admin'], unique=False)
    op.create_index('idx_app_user_is_super_admin', 'app_user', ['is_super_admin'], unique=False)
    op.create_index('idx_app_user_role', 'app_user', ['role'], unique=False)
    op.create_table('market_close',
    sa.Column('symbol', sa.String(), nullable=False),
    sa.Column('date', sa.Date(), nullable=False),
    sa.Column('close', sa.Numeric(), nullable=False),
    sa.PrimaryKeyConstraint('symbol', 'date')
    )
    op.create_index('idx_market_close_date', 'market_close', ['date'], unique=False)
    op.create_index('idx_market_close_symbol', 'market_close', ['symbol'], unique=False)
    op.create_table('app_session',
    sa.Column('id', sa.String(), nullable=False),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('last_seen_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_app_session_expires_at', 'app_session', ['expires_at'], unique=False)
    op.create_index('idx_app_session_user_id', 'app_session', ['user_id'], unique=False)
    op.create_table('strategy_run',
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.Column('started_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('stopped_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('status', sa.String(), nullable=False),
    sa.Column('strategy', sa.String(), nullable=True),
    sa.Column('mode', sa.String(), nullable=True),
    sa.Column('broker', sa.String(), nullable=True),
    sa.Column('meta', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.CheckConstraint("status IN ('RUNNING','STOPPED','ERROR')", name='chk_strategy_run_status'),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('run_id'),
    sa.UniqueConstraint('user_id', 'run_id', name='uq_strategy_run_user_run')
    )
    op.create_index('idx_strategy_run_user_created', 'strategy_run', ['user_id', 'created_at'], unique=False)
    op.create_index('idx_strategy_run_user_status', 'strategy_run', ['user_id', 'status'], unique=False)
    op.create_index('uq_one_running_run_per_user', 'strategy_run', ['user_id'], unique=True, postgresql_where=sa.text("status = 'RUNNING'"))
    op.create_table('user_broker',
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('broker', sa.String(), nullable=True),
    sa.Column('connected', sa.Boolean(), server_default=sa.text('false'), nullable=False),
    sa.Column('access_token', sa.Text(), nullable=True),
    sa.Column('connected_at', sa.DateTime(timezone=True), nullable=True),
    sa.Column('api_key', sa.Text(), nullable=True),
    sa.Column('user_name', sa.Text(), nullable=True),
    sa.Column('broker_user_id', sa.Text(), nullable=True),
    sa.Column('pending_broker', sa.Text(), nullable=True),
    sa.Column('pending_api_key', sa.Text(), nullable=True),
    sa.Column('pending_api_secret', sa.Text(), nullable=True),
    sa.Column('pending_started_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('user_id')
    )
    op.create_index('idx_user_broker_broker', 'user_broker', ['broker'], unique=False)
    op.create_index('idx_user_broker_connected', 'user_broker', ['connected'], unique=False)
    op.create_table('zerodha_request_token',
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('request_token', sa.Text(), nullable=False),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('user_id')
    )
    op.create_table('zerodha_session',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('linked_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('api_key', sa.Text(), nullable=True),
    sa.Column('access_token', sa.Text(), nullable=True),
    sa.Column('request_token', sa.Text(), nullable=True),
    sa.Column('user_name', sa.Text(), nullable=True),
    sa.Column('broker_user_id', sa.Text(), nullable=True),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_zerodha_session_linked_at', 'zerodha_session', ['linked_at'], unique=False)
    op.create_index('idx_zerodha_session_user_id', 'zerodha_session', ['user_id'], unique=False)
    op.create_table('engine_event',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('ts', sa.DateTime(timezone=True), nullable=False),
    sa.Column('event', sa.String(), nullable=True),
    sa.Column('data', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.Column('message', sa.Text(), nullable=True),
    sa.Column('meta', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.ForeignKeyConstraint(['run_id'], ['strategy_run.run_id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('idx_engine_event_ts', 'engine_event', ['ts'], unique=False)
    op.create_index('idx_engine_event_user_run_ts', 'engine_event', ['user_id', 'run_id', 'ts'], unique=False)
    op.create_table('engine_state',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('total_invested', sa.Numeric(), nullable=True),
    sa.Column('nifty_units', sa.Numeric(), nullable=True),
    sa.Column('gold_units', sa.Numeric(), nullable=True),
    sa.Column('last_sip_ts', sa.DateTime(timezone=True), nullable=True),
    sa.Column('last_run', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['user_id', 'run_id'], ['strategy_run.user_id', 'strategy_run.run_id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('user_id', 'run_id', name='uq_engine_state_user_run')
    )
    op.create_table('engine_state_paper',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('initial_cash', sa.Numeric(), nullable=True),
    sa.Column('cash', sa.Numeric(), nullable=True),
    sa.Column('total_invested', sa.Numeric(), nullable=True),
    sa.Column('nifty_units', sa.Numeric(), nullable=True),
    sa.Column('gold_units', sa.Numeric(), nullable=True),
    sa.Column('last_sip_ts', sa.DateTime(timezone=True), nullable=True),
    sa.Column('last_run', sa.DateTime(timezone=True), nullable=True),
    sa.Column('sip_frequency_value', sa.Integer(), nullable=True),
    sa.Column('sip_frequency_unit', sa.String(), nullable=True),
    sa.CheckConstraint('cash >= 0', name='chk_engine_state_paper_cash_non_negative'),
    sa.ForeignKeyConstraint(['user_id', 'run_id'], ['strategy_run.user_id', 'strategy_run.run_id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('user_id', 'run_id', name='uq_engine_state_paper_user_run')
    )
    op.create_table('engine_status',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('status', sa.String(), nullable=False),
    sa.Column('last_updated', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['user_id', 'run_id'], ['strategy_run.user_id', 'strategy_run.run_id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('user_id', 'run_id', name='uq_engine_status_user_run')
    )
    op.create_index('idx_engine_status_user_run', 'engine_status', ['user_id', 'run_id'], unique=False)
    op.create_table('event_ledger',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False),
    sa.Column('logical_time', sa.DateTime(timezone=True), nullable=False),
    sa.Column('event', sa.String(), nullable=False),
    sa.Column('nifty_units', sa.Numeric(), nullable=True),
    sa.Column('gold_units', sa.Numeric(), nullable=True),
    sa.Column('nifty_price', sa.Numeric(), nullable=True),
    sa.Column('gold_price', sa.Numeric(), nullable=True),
    sa.Column('amount', sa.Numeric(), nullable=True),
    sa.ForeignKeyConstraint(['user_id', 'run_id'], ['strategy_run.user_id', 'strategy_run.run_id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('user_id', 'run_id', 'event', 'logical_time', name='uq_event_ledger_event_time')
    )
    op.create_index('idx_event_ledger_ts', 'event_ledger', ['timestamp'], unique=False)
    op.create_index('idx_event_ledger_user_run_ts', 'event_ledger', ['user_id', 'run_id', 'timestamp'], unique=False)
    op.create_table('mtm_ledger',
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False),
    sa.Column('logical_time', sa.DateTime(timezone=True), nullable=False),
    sa.Column('nifty_units', sa.Numeric(), nullable=True),
    sa.Column('gold_units', sa.Numeric(), nullable=True),
    sa.Column('nifty_price', sa.Numeric(), nullable=True),
    sa.Column('gold_price', sa.Numeric(), nullable=True),
    sa.Column('nifty_value', sa.Numeric(), nullable=True),
    sa.Column('gold_value', sa.Numeric(), nullable=True),
    sa.Column('portfolio_value', sa.Numeric(), nullable=True),
    sa.Column('total_invested', sa.Numeric(), nullable=True),
    sa.Column('pnl', sa.Numeric(), nullable=True),
    sa.ForeignKeyConstraint(['user_id', 'run_id'], ['strategy_run.user_id', 'strategy_run.run_id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('user_id', 'run_id', 'logical_time')
    )
    op.create_index('idx_mtm_ledger_ts', 'mtm_ledger', ['timestamp'], unique=False)
    op.create_index('idx_mtm_ledger_user_run_ts', 'mtm_ledger', ['user_id', 'run_id', 'timestamp'], unique=False)
    op.create_table('paper_broker_account',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('cash', sa.Numeric(), nullable=False),
    sa.CheckConstraint('cash >= 0', name='chk_paper_broker_cash_non_negative'),
    sa.ForeignKeyConstraint(['user_id', 'run_id'], ['strategy_run.user_id', 'strategy_run.run_id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('user_id', 'run_id', name='uq_paper_broker_account_user_run')
    )
    op.create_table('paper_equity_curve',
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False),
    sa.Column('logical_time', sa.DateTime(timezone=True), nullable=False),
    sa.Column('equity', sa.Numeric(), nullable=False),
    sa.Column('pnl', sa.Numeric(), nullable=True),
    sa.ForeignKeyConstraint(['user_id', 'run_id'], ['strategy_run.user_id', 'strategy_run.run_id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('user_id', 'run_id', 'logical_time')
    )
    op.create_index('idx_paper_equity_curve_ts', 'paper_equity_curve', ['timestamp'], unique=False)
    op.create_index('idx_paper_equity_curve_user_run_ts', 'paper_equity_curve', ['user_id', 'run_id', 'timestamp'], unique=False)
    op.create_table('paper_order',
    sa.Column('id', sa.String(), nullable=False),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('symbol', sa.String(), nullable=False),
    sa.Column('side', sa.String(), nullable=False),
    sa.Column('qty', sa.Numeric(), nullable=False),
    sa.Column('price', sa.Numeric(), nullable=True),
    sa.Column('status', sa.String(), nullable=False),
    sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False),
    sa.Column('logical_time', sa.DateTime(timezone=True), nullable=False),
    sa.CheckConstraint('price >= 0', name='chk_paper_order_price_non_negative'),
    sa.CheckConstraint('qty > 0', name='chk_paper_order_qty_positive'),
    sa.ForeignKeyConstraint(['user_id', 'run_id'], ['strategy_run.user_id', 'strategy_run.run_id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('user_id', 'run_id', 'id', name='uq_paper_order_scope_id'),
    sa.UniqueConstraint('user_id', 'run_id', 'logical_time', 'symbol', 'side', name='uq_paper_order_logical_key')
    )
    op.create_index('idx_paper_order_ts', 'paper_order', ['timestamp'], unique=False)
    op.create_index('idx_paper_order_user_run_ts', 'paper_order', ['user_id', 'run_id', 'timestamp'], unique=False)
    op.create_table('paper_position',
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('symbol', sa.String(), nullable=False),
    sa.Column('qty', sa.Numeric(), nullable=False),
    sa.Column('avg_price', sa.Numeric(), nullable=True),
    sa.Column('last_price', sa.Numeric(), nullable=True),
    sa.Column('updated_at', sa.DateTime(timezone=True), server_default=sa.text('now()'), nullable=False),
    sa.CheckConstraint('qty > 0', name='chk_paper_position_qty_positive'),
    sa.ForeignKeyConstraint(['user_id', 'run_id'], ['strategy_run.user_id', 'strategy_run.run_id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('user_id', 'run_id', 'symbol'),
    sa.UniqueConstraint('user_id', 'run_id', 'symbol', name='uq_paper_position_scope')
    )
    op.create_index('idx_paper_position_user_run', 'paper_position', ['user_id', 'run_id'], unique=False)
    op.create_table('strategy_config',
    sa.Column('id', sa.BigInteger(), autoincrement=True, nullable=False),
    sa.Column('strategy', sa.String(), nullable=True),
    sa.Column('sip_amount', sa.Numeric(), nullable=True),
    sa.Column('sip_frequency_value', sa.Integer(), nullable=True),
    sa.Column('sip_frequency_unit', sa.String(), nullable=True),
    sa.Column('mode', sa.String(), nullable=True),
    sa.Column('broker', sa.String(), nullable=True),
    sa.Column('active', sa.Boolean(), nullable=True),
    sa.Column('frequency', sa.Text(), nullable=True),
    sa.Column('frequency_days', sa.Integer(), nullable=True),
    sa.Column('unit', sa.String(), nullable=True),
    sa.Column('next_run', sa.DateTime(timezone=True), nullable=True),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.ForeignKeyConstraint(['run_id'], ['strategy_run.run_id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('user_id', 'run_id', name='uq_strategy_config_user_run')
    )
    op.create_table('strategy_log',
    sa.Column('seq', sa.BigInteger(), nullable=False),
    sa.Column('ts', sa.DateTime(timezone=True), nullable=False),
    sa.Column('level', sa.String(), nullable=True),
    sa.Column('category', sa.String(), nullable=True),
    sa.Column('event', sa.String(), nullable=True),
    sa.Column('message', sa.Text(), nullable=True),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('meta', postgresql.JSONB(astext_type=sa.Text()), nullable=True),
    sa.ForeignKeyConstraint(['run_id'], ['strategy_run.run_id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('seq')
    )
    op.create_index('idx_strategy_log_event', 'strategy_log', ['event'], unique=False)
    op.create_index('idx_strategy_log_ts', 'strategy_log', ['ts'], unique=False)
    op.create_index('idx_strategy_log_user_run_ts', 'strategy_log', ['user_id', 'run_id', 'ts'], unique=False)
    op.create_table('paper_trade',
    sa.Column('id', sa.String(), nullable=False),
    sa.Column('order_id', sa.String(), nullable=True),
    sa.Column('user_id', sa.String(), nullable=False),
    sa.Column('run_id', sa.String(), nullable=False),
    sa.Column('symbol', sa.String(), nullable=False),
    sa.Column('side', sa.String(), nullable=False),
    sa.Column('qty', sa.Numeric(), nullable=False),
    sa.Column('price', sa.Numeric(), nullable=False),
    sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False),
    sa.Column('logical_time', sa.DateTime(timezone=True), nullable=False),
    sa.CheckConstraint('price >= 0', name='chk_paper_trade_price_non_negative'),
    sa.CheckConstraint('qty > 0', name='chk_paper_trade_qty_positive'),
    sa.ForeignKeyConstraint(['user_id', 'run_id', 'order_id'], ['paper_order.user_id', 'paper_order.run_id', 'paper_order.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id', 'run_id'], ['strategy_run.user_id', 'strategy_run.run_id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['user_id'], ['app_user.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('user_id', 'run_id', 'id', name='uq_paper_trade_scope_id'),
    sa.UniqueConstraint('user_id', 'run_id', 'logical_time', 'symbol', 'side', name='uq_paper_trade_logical_key')
    )
    op.create_index('idx_paper_trade_ts', 'paper_trade', ['timestamp'], unique=False)
    op.create_index('idx_paper_trade_user_run_ts', 'paper_trade', ['user_id', 'run_id', 'timestamp'], unique=False)
    # admin views and protections
    op.execute(
        """
        CREATE OR REPLACE FUNCTION prevent_super_admin_delete()
        RETURNS trigger AS $$
        BEGIN
          IF OLD.role = 'SUPER_ADMIN' OR OLD.is_super_admin THEN
            RAISE EXCEPTION 'cannot delete super admin user';
          END IF;
          RETURN OLD;
        END;
        $$ LANGUAGE plpgsql;
        """
    )
    op.execute(
        """
        DO $$
        BEGIN
          IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_prevent_super_admin_delete') THEN
            CREATE TRIGGER trg_prevent_super_admin_delete
            BEFORE DELETE ON app_user
            FOR EACH ROW
            EXECUTE FUNCTION prevent_super_admin_delete();
          END IF;
        END $$;
        """
    )
    op.create_index('idx_event_ledger_user_run_logical', 'event_ledger', ['user_id', 'run_id', 'logical_time'], unique=False)
    op.execute(
        """
        CREATE OR REPLACE VIEW admin_user_metrics AS
        WITH session_stats AS (
          SELECT
            user_id,
            MIN(created_at) AS first_session_at,
            MAX(COALESCE(last_seen_at, created_at)) AS last_login_at
          FROM app_session
          GROUP BY user_id
        ),
        run_stats AS (
          SELECT
            user_id,
            COUNT(*) AS runs_count,
            MAX(CASE WHEN status = 'RUNNING' THEN run_id END) AS active_run_id,
            MAX(CASE WHEN status = 'RUNNING' THEN status END) AS active_run_status,
            MIN(created_at) AS first_run_at
          FROM strategy_run
          GROUP BY user_id
        ),
        broker_stats AS (
          SELECT user_id, BOOL_OR(connected) AS broker_connected
          FROM user_broker
          GROUP BY user_id
        )
        SELECT
          u.id AS user_id,
          u.username,
          u.role,
          (u.role IN ('ADMIN','SUPER_ADMIN')) AS is_admin,
          COALESCE(session_stats.first_session_at, run_stats.first_run_at) AS created_at,
          session_stats.last_login_at,
          COALESCE(run_stats.runs_count, 0) AS runs_count,
          run_stats.active_run_id,
          run_stats.active_run_status,
          COALESCE(broker_stats.broker_connected, FALSE) AS broker_connected
        FROM app_user u
        LEFT JOIN session_stats ON session_stats.user_id = u.id
        LEFT JOIN run_stats ON run_stats.user_id = u.id
        LEFT JOIN broker_stats ON broker_stats.user_id = u.id;
        """
    )
    op.execute(
        """
        CREATE OR REPLACE VIEW admin_run_metrics AS
        WITH order_stats AS (
          SELECT user_id, run_id, COUNT(*) AS order_count, MAX("timestamp") AS last_order_time
          FROM paper_order
          GROUP BY user_id, run_id
        ),
        trade_stats AS (
          SELECT user_id, run_id, COUNT(*) AS trade_count, MAX("timestamp") AS last_trade_time
          FROM paper_trade
          GROUP BY user_id, run_id
        ),
        event_stats AS (
          SELECT
            user_id,
            run_id,
            MAX("timestamp") AS last_event_time,
            MAX(CASE WHEN event = 'SIP_EXECUTED' THEN "timestamp" END) AS last_sip_time
          FROM event_ledger
          GROUP BY user_id, run_id
        ),
        equity_latest AS (
          SELECT DISTINCT ON (user_id, run_id)
            user_id,
            run_id,
            equity AS equity_latest,
            pnl AS pnl_latest,
            "timestamp" AS equity_ts
          FROM paper_equity_curve
          ORDER BY user_id, run_id, "timestamp" DESC
        ),
        mtm_latest AS (
          SELECT DISTINCT ON (user_id, run_id)
            user_id,
            run_id,
            "timestamp" AS mtm_ts
          FROM mtm_ledger
          ORDER BY user_id, run_id, "timestamp" DESC
        ),
        log_latest AS (
          SELECT user_id, run_id, MAX(ts) AS last_log_time
          FROM strategy_log
          GROUP BY user_id, run_id
        ),
        engine_latest AS (
          SELECT user_id, run_id, MAX(ts) AS last_engine_time
          FROM engine_event
          GROUP BY user_id, run_id
        ),
        activity AS (
          SELECT user_id, run_id, MAX(ts) AS last_event_time
          FROM (
            SELECT user_id, run_id, ts FROM engine_event
            UNION ALL
            SELECT user_id, run_id, ts FROM strategy_log
            UNION ALL
            SELECT user_id, run_id, "timestamp" AS ts FROM paper_order
            UNION ALL
            SELECT user_id, run_id, "timestamp" AS ts FROM paper_trade
            UNION ALL
            SELECT user_id, run_id, "timestamp" AS ts FROM mtm_ledger
            UNION ALL
            SELECT user_id, run_id, "timestamp" AS ts FROM paper_equity_curve
            UNION ALL
            SELECT user_id, run_id, "timestamp" AS ts FROM event_ledger
          ) t
          GROUP BY user_id, run_id
        )
        SELECT
          sr.run_id,
          sr.user_id,
          sr.status,
          sr.created_at,
          sr.started_at,
          sr.stopped_at,
          sr.strategy,
          sr.mode,
          sr.broker,
          sc.sip_amount,
          sc.sip_frequency_value,
          sc.sip_frequency_unit,
          sc.next_run AS next_sip_time,
          activity.last_event_time,
          event_stats.last_sip_time,
          COALESCE(order_stats.order_count, 0) AS order_count,
          COALESCE(trade_stats.trade_count, 0) AS trade_count,
          equity_latest.equity_latest,
          equity_latest.pnl_latest
        FROM strategy_run sr
        LEFT JOIN strategy_config sc
          ON sc.user_id = sr.user_id AND sc.run_id = sr.run_id
        LEFT JOIN order_stats
          ON order_stats.user_id = sr.user_id AND order_stats.run_id = sr.run_id
        LEFT JOIN trade_stats
          ON trade_stats.user_id = sr.user_id AND trade_stats.run_id = sr.run_id
        LEFT JOIN event_stats
          ON event_stats.user_id = sr.user_id AND event_stats.run_id = sr.run_id
        LEFT JOIN equity_latest
          ON equity_latest.user_id = sr.user_id AND equity_latest.run_id = sr.run_id
        LEFT JOIN mtm_latest
          ON mtm_latest.user_id = sr.user_id AND mtm_latest.run_id = sr.run_id
        LEFT JOIN log_latest
          ON log_latest.user_id = sr.user_id AND log_latest.run_id = sr.run_id
        LEFT JOIN engine_latest
          ON engine_latest.user_id = sr.user_id AND engine_latest.run_id = sr.run_id
        LEFT JOIN activity
          ON activity.user_id = sr.user_id AND activity.run_id = sr.run_id;
        """
    )
    op.execute(
        """
        CREATE OR REPLACE VIEW admin_engine_health AS
        WITH activity AS (
          SELECT user_id, run_id, MAX(ts) AS last_event_time
          FROM (
            SELECT user_id, run_id, ts FROM engine_event
            UNION ALL
            SELECT user_id, run_id, ts FROM strategy_log
            UNION ALL
            SELECT user_id, run_id, "timestamp" AS ts FROM event_ledger
          ) t
          GROUP BY user_id, run_id
        )
        SELECT
          sr.run_id,
          sr.user_id,
          sr.status,
          activity.last_event_time,
          es.status AS engine_status,
          es.last_updated AS engine_status_ts
        FROM strategy_run sr
        LEFT JOIN activity
          ON activity.user_id = sr.user_id AND activity.run_id = sr.run_id
        LEFT JOIN engine_status es
          ON es.user_id = sr.user_id AND es.run_id = sr.run_id;
        """
    )
    op.execute(
        """
        CREATE OR REPLACE VIEW admin_order_stats AS
        SELECT
          user_id,
          run_id,
          COUNT(*) AS total_orders,
          COUNT(*) FILTER (WHERE "timestamp" >= now() - interval '24 hours') AS orders_last_24h,
          COUNT(*) FILTER (WHERE status = 'FILLED') AS filled_orders
        FROM paper_order
        GROUP BY user_id, run_id;
        """
    )
    op.execute(
        """
        CREATE OR REPLACE VIEW admin_ledger_stats AS
        WITH mtm_latest AS (
          SELECT DISTINCT ON (user_id, run_id)
            user_id,
            run_id,
            portfolio_value,
            pnl,
            "timestamp" AS mtm_ts
          FROM mtm_ledger
          ORDER BY user_id, run_id, "timestamp" DESC
        ),
        equity_latest AS (
          SELECT DISTINCT ON (user_id, run_id)
            user_id,
            run_id,
            equity,
            pnl,
            "timestamp" AS equity_ts
          FROM paper_equity_curve
          ORDER BY user_id, run_id, "timestamp" DESC
        )
        SELECT
          sr.user_id,
          sr.run_id,
          mtm_latest.portfolio_value AS mtm_value,
          mtm_latest.pnl AS mtm_pnl,
          mtm_latest.mtm_ts,
          equity_latest.equity AS equity_value,
          equity_latest.pnl AS equity_pnl,
          equity_latest.equity_ts
        FROM strategy_run sr
        LEFT JOIN mtm_latest
          ON mtm_latest.user_id = sr.user_id AND mtm_latest.run_id = sr.run_id
        LEFT JOIN equity_latest
          ON equity_latest.user_id = sr.user_id AND equity_latest.run_id = sr.run_id;
        """
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.execute("DROP VIEW IF EXISTS admin_ledger_stats;")
    op.execute("DROP VIEW IF EXISTS admin_order_stats;")
    op.execute("DROP VIEW IF EXISTS admin_engine_health;")
    op.execute("DROP VIEW IF EXISTS admin_run_metrics;")
    op.execute("DROP VIEW IF EXISTS admin_user_metrics;")
    op.execute("DROP TRIGGER IF EXISTS trg_prevent_super_admin_delete ON app_user;")
    op.execute("DROP FUNCTION IF EXISTS prevent_super_admin_delete;")
    op.drop_index('idx_paper_trade_user_run_ts', table_name='paper_trade')
    op.drop_index('idx_paper_trade_ts', table_name='paper_trade')
    op.drop_table('paper_trade')
    op.drop_index('idx_strategy_log_user_run_ts', table_name='strategy_log')
    op.drop_index('idx_strategy_log_ts', table_name='strategy_log')
    op.drop_index('idx_strategy_log_event', table_name='strategy_log')
    op.drop_table('strategy_log')
    op.drop_table('strategy_config')
    op.drop_index('idx_paper_position_user_run', table_name='paper_position')
    op.drop_table('paper_position')
    op.drop_index('idx_paper_order_user_run_ts', table_name='paper_order')
    op.drop_index('idx_paper_order_ts', table_name='paper_order')
    op.drop_table('paper_order')
    op.drop_index('idx_paper_equity_curve_user_run_ts', table_name='paper_equity_curve')
    op.drop_index('idx_paper_equity_curve_ts', table_name='paper_equity_curve')
    op.drop_table('paper_equity_curve')
    op.drop_table('paper_broker_account')
    op.drop_index('idx_mtm_ledger_user_run_ts', table_name='mtm_ledger')
    op.drop_index('idx_mtm_ledger_ts', table_name='mtm_ledger')
    op.drop_table('mtm_ledger')
    op.drop_index('idx_event_ledger_user_run_logical', table_name='event_ledger')
    op.drop_index('idx_event_ledger_user_run_ts', table_name='event_ledger')
    op.drop_index('idx_event_ledger_ts', table_name='event_ledger')
    op.drop_table('event_ledger')
    op.drop_index('idx_engine_status_user_run', table_name='engine_status')
    op.drop_table('engine_status')
    op.drop_table('engine_state_paper')
    op.drop_table('engine_state')
    op.drop_index('idx_engine_event_user_run_ts', table_name='engine_event')
    op.drop_index('idx_engine_event_ts', table_name='engine_event')
    op.drop_table('engine_event')
    op.drop_index('idx_zerodha_session_user_id', table_name='zerodha_session')
    op.drop_index('idx_zerodha_session_linked_at', table_name='zerodha_session')
    op.drop_table('zerodha_session')
    op.drop_table('zerodha_request_token')
    op.drop_index('idx_user_broker_connected', table_name='user_broker')
    op.drop_index('idx_user_broker_broker', table_name='user_broker')
    op.drop_table('user_broker')
    op.drop_index('uq_one_running_run_per_user', table_name='strategy_run', postgresql_where=sa.text("status = 'RUNNING'"))
    op.drop_index('idx_strategy_run_user_status', table_name='strategy_run')
    op.drop_index('idx_strategy_run_user_created', table_name='strategy_run')
    op.drop_table('strategy_run')
    op.drop_index('idx_app_session_user_id', table_name='app_session')
    op.drop_index('idx_app_session_expires_at', table_name='app_session')
    op.drop_table('app_session')
    op.drop_index('idx_market_close_symbol', table_name='market_close')
    op.drop_index('idx_market_close_date', table_name='market_close')
    op.drop_table('market_close')
    op.drop_index('idx_app_user_role', table_name='app_user')
    op.drop_index('idx_app_user_is_super_admin', table_name='app_user')
    op.drop_index('idx_app_user_is_admin', table_name='app_user')
    op.drop_table('app_user')
    op.drop_table('admin_role_audit')
    op.drop_table('admin_audit_log')
    # ### end Alembic commands ###