BEGIN;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_paper_order_qty_positive'
    ) THEN
        ALTER TABLE paper_order
            ADD CONSTRAINT chk_paper_order_qty_positive CHECK (qty > 0) NOT VALID;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_paper_order_price_non_negative'
    ) THEN
        ALTER TABLE paper_order
            ADD CONSTRAINT chk_paper_order_price_non_negative CHECK (price >= 0) NOT VALID;
    END IF;
END $$;

ALTER TABLE paper_order
    ALTER COLUMN timestamp SET NOT NULL;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_paper_trade_qty_positive'
    ) THEN
        ALTER TABLE paper_trade
            ADD CONSTRAINT chk_paper_trade_qty_positive CHECK (qty > 0) NOT VALID;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_paper_trade_price_non_negative'
    ) THEN
        ALTER TABLE paper_trade
            ADD CONSTRAINT chk_paper_trade_price_non_negative CHECK (price >= 0) NOT VALID;
    END IF;
END $$;

ALTER TABLE paper_trade
    ALTER COLUMN timestamp SET NOT NULL;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_paper_position_qty_positive'
    ) THEN
        ALTER TABLE paper_position
            ADD CONSTRAINT chk_paper_position_qty_positive CHECK (qty > 0) NOT VALID;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_paper_broker_cash_non_negative'
    ) THEN
        ALTER TABLE paper_broker_account
            ADD CONSTRAINT chk_paper_broker_cash_non_negative CHECK (cash >= 0) NOT VALID;
    END IF;
END $$;

ALTER TABLE paper_equity_curve
    ALTER COLUMN timestamp SET NOT NULL;

ALTER TABLE mtm_ledger
    ALTER COLUMN timestamp SET NOT NULL;

ALTER TABLE event_ledger
    ALTER COLUMN timestamp SET NOT NULL;

ALTER TABLE engine_event
    ALTER COLUMN ts SET NOT NULL;

ALTER TABLE strategy_log
    ALTER COLUMN ts SET NOT NULL;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'chk_engine_state_paper_cash_non_negative'
    ) THEN
        ALTER TABLE engine_state_paper
            ADD CONSTRAINT chk_engine_state_paper_cash_non_negative CHECK (cash >= 0) NOT VALID;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'fk_paper_trade_order'
    ) THEN
        ALTER TABLE paper_trade
            ADD CONSTRAINT fk_paper_trade_order
            FOREIGN KEY (order_id) REFERENCES paper_order(id)
            ON DELETE SET NULL;
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_paper_order_ts ON paper_order(timestamp);
CREATE INDEX IF NOT EXISTS idx_paper_trade_ts ON paper_trade(timestamp);
CREATE INDEX IF NOT EXISTS idx_paper_equity_curve_ts ON paper_equity_curve(timestamp);
CREATE INDEX IF NOT EXISTS idx_mtm_ledger_ts ON mtm_ledger(timestamp);
CREATE INDEX IF NOT EXISTS idx_event_ledger_ts ON event_ledger(timestamp);
CREATE INDEX IF NOT EXISTS idx_strategy_log_ts ON strategy_log(ts);
CREATE INDEX IF NOT EXISTS idx_engine_event_ts ON engine_event(ts);
CREATE INDEX IF NOT EXISTS idx_app_session_user_expires ON app_session(user_id, expires_at);

COMMIT;