"""Checkpoint C3: analyse des consommations et alertes."""
from alembic import op
import sqlalchemy as sa

revision = "p1e2f3g4h5i6"
down_revision = "o0d1e2f3g4h5"
branch_labels = None
depends_on = None

def upgrade():
    op.create_table("meter_heating_regimes", sa.Column("id", sa.Integer, primary_key=True), sa.Column("meter_id", sa.Integer, nullable=False), sa.Column("network_name", sa.String(120), nullable=False), sa.Column("regime", sa.String(20), nullable=False), sa.Column("valid_from", sa.Date, nullable=False), sa.Column("valid_to", sa.Date), sa.Column("notes", sa.Text), sa.ForeignKeyConstraint(["meter_id"], ["meters.id"], ondelete="CASCADE"), sa.CheckConstraint("regime in ('NORMAL','REDUCED','STOP')", name="ck_meter_heating_regime"))
    op.create_table("meter_alert_rules", sa.Column("id", sa.Integer, primary_key=True), sa.Column("meter_id", sa.Integer, nullable=False), sa.Column("metric", sa.String(60), nullable=False), sa.Column("period", sa.String(10), nullable=False, server_default="DAY"), sa.Column("context_filter", sa.String(40)), sa.Column("operator", sa.String(2), nullable=False), sa.Column("threshold", sa.Float, nullable=False), sa.Column("level", sa.String(10), nullable=False), sa.Column("is_active", sa.Boolean, nullable=False, server_default="1"), sa.Column("min_comparable_intervals", sa.Integer, nullable=False, server_default="3"), sa.Column("detect_zero_or_low", sa.Boolean, nullable=False, server_default="0"), sa.ForeignKeyConstraint(["meter_id"], ["meters.id"], ondelete="CASCADE"))
    op.create_table("meter_alerts", sa.Column("id", sa.Integer, primary_key=True), sa.Column("meter_id", sa.Integer, nullable=False), sa.Column("alert_type", sa.String(40), nullable=False), sa.Column("level", sa.String(10), nullable=False), sa.Column("metric", sa.String(60), nullable=False), sa.Column("period", sa.String(10), nullable=False), sa.Column("context_key", sa.String(120)), sa.Column("period_start", sa.Date), sa.Column("period_end", sa.Date), sa.Column("observed_value", sa.Float), sa.Column("reference_value", sa.Float), sa.Column("deviation_percent", sa.Float), sa.Column("comparable_count", sa.Integer), sa.Column("status", sa.String(20), nullable=False, server_default="OPEN"), sa.Column("opened_at", sa.DateTime, nullable=False), sa.Column("closed_at", sa.DateTime), sa.Column("conclusion", sa.String(30)), sa.Column("comment", sa.Text), sa.Column("intervention_id", sa.Integer), sa.ForeignKeyConstraint(["meter_id"], ["meters.id"], ondelete="CASCADE"), sa.ForeignKeyConstraint(["intervention_id"], ["interventions.id"], ondelete="SET NULL"))
    op.create_table("meter_alert_evidence", sa.Column("id", sa.Integer, primary_key=True), sa.Column("alert_id", sa.Integer, nullable=False), sa.Column("reading_start_id", sa.Integer), sa.Column("reading_end_id", sa.Integer), sa.Column("observed_value", sa.Float), sa.Column("detected_at", sa.DateTime, nullable=False), sa.Column("excluded_from_baseline", sa.Boolean, nullable=False, server_default="0"), sa.ForeignKeyConstraint(["alert_id"], ["meter_alerts.id"], ondelete="CASCADE"), sa.ForeignKeyConstraint(["reading_start_id"], ["meter_readings.id"], ondelete="SET NULL"), sa.ForeignKeyConstraint(["reading_end_id"], ["meter_readings.id"], ondelete="SET NULL"))
    op.create_table("meter_gas_conversions", sa.Column("id", sa.Integer, primary_key=True), sa.Column("meter_id", sa.Integer, nullable=False), sa.Column("coefficient_kwh_per_m3", sa.Float, nullable=False), sa.Column("valid_from", sa.Date, nullable=False), sa.Column("valid_to", sa.Date), sa.Column("origin", sa.String(20), nullable=False), sa.Column("volume_m3", sa.Float), sa.Column("billed_kwh", sa.Float), sa.ForeignKeyConstraint(["meter_id"], ["meters.id"], ondelete="CASCADE"))
    op.create_table("meter_tariffs", sa.Column("id", sa.Integer, primary_key=True), sa.Column("meter_id", sa.Integer, nullable=False), sa.Column("energy_type", sa.String(30), nullable=False), sa.Column("unit_price", sa.Float, nullable=False), sa.Column("unit", sa.String(20), nullable=False), sa.Column("valid_from", sa.Date, nullable=False), sa.Column("valid_to", sa.Date), sa.ForeignKeyConstraint(["meter_id"], ["meters.id"], ondelete="CASCADE"))
    for table, cols in {"meter_heating_regimes":["meter_id","valid_from","valid_to"],"meter_alert_rules":["meter_id","is_active"],"meter_alerts":["meter_id","status","intervention_id"],"meter_alert_evidence":["alert_id","reading_end_id"],"meter_gas_conversions":["meter_id","valid_from"],"meter_tariffs":["meter_id","valid_from"]}.items():
        for col in cols: op.create_index(f"ix_{table}_{col}", table, [col])

def downgrade():
    for table in ("meter_tariffs","meter_gas_conversions","meter_alert_evidence","meter_alerts","meter_alert_rules","meter_heating_regimes"):
        op.drop_table(table)
