# QC Élection Forecast — Plateforme de prévision électorale du Québec 2026
# Auteur : Simon-Pierre Boucher
# Contact : contact@spboucher.ai
# https://www.qc-election.com
"""Schéma de données — chaque valeur conserve sa provenance (source, URL, dates)."""
from __future__ import annotations

from datetime import date, datetime, timezone

from sqlalchemy import (JSON, Boolean, Date, DateTime, Float, ForeignKey, Index, Integer,
                        String, Text, UniqueConstraint)
from sqlalchemy.orm import Mapped, mapped_column, relationship

from .db import Base


def utcnow() -> datetime:
    return datetime.now(timezone.utc)


class Election(Base):
    __tablename__ = "elections"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(120), unique=True)
    election_date: Mapped[date] = mapped_column(Date, index=True)
    total_seats: Mapped[int] = mapped_column(Integer)
    majority_seats: Mapped[int] = mapped_column(Integer)
    is_target: Mapped[bool] = mapped_column(Boolean, default=False)
    # Résultat provincial réel {party: pct} — rempli pour les élections passées
    actual_result: Mapped[dict | None] = mapped_column(JSON, nullable=True)


class Party(Base):
    __tablename__ = "parties"
    id: Mapped[int] = mapped_column(primary_key=True)
    code: Mapped[str] = mapped_column(String(8), unique=True)
    name: Mapped[str] = mapped_column(String(80))
    leader: Mapped[str | None] = mapped_column(String(80), nullable=True)
    color: Mapped[str | None] = mapped_column(String(9), nullable=True)


class District(Base):
    __tablename__ = "districts"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(80), unique=True)
    region: Mapped[str] = mapped_column(String(60), index=True)
    # Résultat 2022 transposé (approx.) {party: pct}
    baseline_shares: Mapped[dict] = mapped_column(JSON)
    baseline_source: Mapped[str] = mapped_column(String(200), default="")
    incumbent_party: Mapped[str | None] = mapped_column(String(8), nullable=True)
    incumbent_running: Mapped[bool] = mapped_column(Boolean, default=True)
    current_holder_party: Mapped[str | None] = mapped_column(String(8), nullable=True)
    turnout_2022: Mapped[float | None] = mapped_column(Float, nullable=True)
    is_new_2026: Mapped[bool] = mapped_column(Boolean, default=False)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)


class Candidate(Base):
    __tablename__ = "candidates"
    id: Mapped[int] = mapped_column(primary_key=True)
    district_id: Mapped[int] = mapped_column(ForeignKey("districts.id"), index=True)
    party: Mapped[str] = mapped_column(String(8))
    party_full: Mapped[str | None] = mapped_column(String(120), nullable=True)
    name: Mapped[str] = mapped_column(String(120))
    is_incumbent: Mapped[bool] = mapped_column(Boolean, default=False)
    source_url: Mapped[str | None] = mapped_column(Text, nullable=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)


class Pollster(Base):
    __tablename__ = "pollsters"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(80), unique=True)
    default_mode: Mapped[str] = mapped_column(String(20), default="unknown")
    transparency: Mapped[str | None] = mapped_column(String(20), nullable=True)


class Poll(Base):
    __tablename__ = "polls"
    __table_args__ = (
        UniqueConstraint("pollster_id", "field_end", "sample_size", name="uq_poll_dedupe"),
        Index("ix_polls_field_end", "field_end"),
    )
    id: Mapped[int] = mapped_column(primary_key=True)
    election_id: Mapped[int] = mapped_column(ForeignKey("elections.id"), index=True)
    pollster_id: Mapped[int] = mapped_column(ForeignKey("pollsters.id"))
    sponsor: Mapped[str | None] = mapped_column(String(120), nullable=True)
    field_start: Mapped[date | None] = mapped_column(Date, nullable=True)
    field_end: Mapped[date] = mapped_column(Date)
    published: Mapped[date | None] = mapped_column(Date, nullable=True)
    sample_size: Mapped[int | None] = mapped_column(Integer, nullable=True)
    population: Mapped[str] = mapped_column(String(30), default="adults")  # adults/RV/LV
    mode: Mapped[str] = mapped_column(String(20), default="unknown")
    region: Mapped[str] = mapped_column(String(60), default="QC")  # provincial par défaut
    moe: Mapped[float | None] = mapped_column(Float, nullable=True)
    undecided: Mapped[float | None] = mapped_column(Float, nullable=True)
    # Provenance
    source_name: Mapped[str] = mapped_column(String(120), default="")
    source_url: Mapped[str | None] = mapped_column(Text, nullable=True)
    accessed_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)
    methodology_notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    excluded: Mapped[bool] = mapped_column(Boolean, default=False)
    exclusion_reason: Mapped[str | None] = mapped_column(String(200), nullable=True)

    pollster: Mapped["Pollster"] = relationship()
    results: Mapped[list["PollResult"]] = relationship(cascade="all, delete-orphan")


class PollResult(Base):
    __tablename__ = "poll_results"
    __table_args__ = (UniqueConstraint("poll_id", "party", name="uq_poll_party"),)
    id: Mapped[int] = mapped_column(primary_key=True)
    poll_id: Mapped[int] = mapped_column(ForeignKey("polls.id"), index=True)
    party: Mapped[str] = mapped_column(String(8))
    raw_value: Mapped[float] = mapped_column(Float)         # valeur publiée
    normalized_value: Mapped[float] = mapped_column(Float)  # après renormalisation


class PollsterRating(Base):
    __tablename__ = "pollster_ratings"
    __table_args__ = (UniqueConstraint("pollster_id", "computed_for", name="uq_rating"),)
    id: Mapped[int] = mapped_column(primary_key=True)
    pollster_id: Mapped[int] = mapped_column(ForeignKey("pollsters.id"))
    computed_for: Mapped[str] = mapped_column(String(40))   # ex.: "2026-general"
    n_polls: Mapped[int] = mapped_column(Integer, default=0)
    mae_pp: Mapped[float | None] = mapped_column(Float, nullable=True)
    house_effects: Mapped[dict] = mapped_column(JSON, default=dict)  # {party: pp}
    weight_multiplier: Mapped[float] = mapped_column(Float, default=1.0)
    detail: Mapped[dict] = mapped_column(JSON, default=dict)
    computed_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)

    pollster: Mapped["Pollster"] = relationship()


class HistoricalDistrictResult(Base):
    __tablename__ = "historical_results"
    __table_args__ = (UniqueConstraint("election_id", "district_name", "party", name="uq_hist"),)
    id: Mapped[int] = mapped_column(primary_key=True)
    election_id: Mapped[int] = mapped_column(ForeignKey("elections.id"), index=True)
    district_name: Mapped[str] = mapped_column(String(80), index=True)
    party: Mapped[str] = mapped_column(String(8))
    pct: Mapped[float] = mapped_column(Float)
    source_url: Mapped[str | None] = mapped_column(Text, nullable=True)


class ForecastRun(Base):
    __tablename__ = "forecast_runs"
    id: Mapped[int] = mapped_column(primary_key=True)
    run_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)
    as_of: Mapped[date] = mapped_column(Date, index=True)   # date "vue" par le modèle
    model_version: Mapped[str] = mapped_column(String(20))
    n_polls_used: Mapped[int] = mapped_column(Integer)
    n_simulations: Mapped[int] = mapped_column(Integer)
    is_backtest: Mapped[bool] = mapped_column(Boolean, default=False)
    label: Mapped[str | None] = mapped_column(String(80), nullable=True)
    # Résumés complets sérialisés (nowcast, forecast, sièges, distributions, diagnostics)
    national: Mapped[dict] = mapped_column(JSON)
    seats: Mapped[dict] = mapped_column(JSON)
    diagnostics: Mapped[dict] = mapped_column(JSON, default=dict)

    district_results: Mapped[list["ForecastDistrictResult"]] = relationship(
        cascade="all, delete-orphan")


class ForecastDistrictResult(Base):
    __tablename__ = "forecast_district_results"
    __table_args__ = (Index("ix_fdr_run", "run_id"),)
    id: Mapped[int] = mapped_column(primary_key=True)
    run_id: Mapped[int] = mapped_column(ForeignKey("forecast_runs.id"))
    district_name: Mapped[str] = mapped_column(String(80))
    # {party: {mean, lo, hi, win_prob}}, favori, catégorie Safe/Likely/Lean/Toss-up
    detail: Mapped[dict] = mapped_column(JSON)
    favorite: Mapped[str] = mapped_column(String(8))
    category: Mapped[str] = mapped_column(String(12))


class SentimentDocument(Base):
    __tablename__ = "sentiment_documents"
    __table_args__ = (UniqueConstraint("url", name="uq_sent_url"),)
    id: Mapped[int] = mapped_column(primary_key=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)
    published: Mapped[datetime | None] = mapped_column(DateTime, nullable=True, index=True)
    source: Mapped[str] = mapped_column(String(80))
    url: Mapped[str] = mapped_column(Text)
    title: Mapped[str] = mapped_column(Text)
    summary: Mapped[str | None] = mapped_column(Text, nullable=True)
    cluster_key: Mapped[str | None] = mapped_column(String(64), nullable=True, index=True)

    scores: Mapped[list["SentimentScore"]] = relationship(cascade="all, delete-orphan")


class SentimentScore(Base):
    __tablename__ = "sentiment_scores"
    id: Mapped[int] = mapped_column(primary_key=True)
    document_id: Mapped[int] = mapped_column(ForeignKey("sentiment_documents.id"), index=True)
    entity: Mapped[str] = mapped_column(String(40), index=True)  # code parti ou nom de chef
    sentiment: Mapped[float] = mapped_column(Float)   # [-1, 1]
    stance: Mapped[str] = mapped_column(String(12))   # pro/anti/neutre/ambigu
    method: Mapped[str] = mapped_column(String(20), default="lexicon")  # lexicon | llm


class NewsEvent(Base):
    __tablename__ = "news_events"
    id: Mapped[int] = mapped_column(primary_key=True)
    event_date: Mapped[date] = mapped_column(Date, index=True)
    title: Mapped[str] = mapped_column(String(200))
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    kind: Mapped[str] = mapped_column(String(30), default="event")  # debate/announcement/anomaly...
    parties: Mapped[list | None] = mapped_column(JSON, nullable=True)
    importance: Mapped[float] = mapped_column(Float, default=0.5)
    detected_by: Mapped[str] = mapped_column(String(30), default="manual")


class ByElection(Base):
    """Élection partielle depuis la dernière générale — vote RÉEL, converti en
    observation nationale bruitée (v2 « au-delà des sondages »)."""
    __tablename__ = "byelections"
    __table_args__ = (UniqueConstraint("district_name", "held_on", name="uq_bye"),)
    id: Mapped[int] = mapped_column(primary_key=True)
    district_name: Mapped[str] = mapped_column(String(80))
    held_on: Mapped[date] = mapped_column(Date, index=True)
    result: Mapped[dict] = mapped_column(JSON)              # {party: pct}
    turnout: Mapped[float | None] = mapped_column(Float, nullable=True)
    winner: Mapped[str] = mapped_column(String(8))
    previous_winner: Mapped[str | None] = mapped_column(String(8), nullable=True)
    source_url: Mapped[str | None] = mapped_column(Text, nullable=True)
    verified: Mapped[bool] = mapped_column(Boolean, default=False)  # confirmé par scrape
    used_in_model: Mapped[bool] = mapped_column(Boolean, default=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)


class WebSignal(Base):
    """Détection de la veille web continue (Firecrawl) : nouveau sondage repéré,
    article pertinent, mesure de satisfaction… Provenance complète."""
    __tablename__ = "web_signals"
    __table_args__ = (UniqueConstraint("url", "kind", name="uq_signal"),)
    id: Mapped[int] = mapped_column(primary_key=True)
    detected_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)
    kind: Mapped[str] = mapped_column(String(30), index=True)  # poll-radar / satisfaction / presse
    title: Mapped[str] = mapped_column(Text)
    url: Mapped[str] = mapped_column(Text)
    snippet: Mapped[str | None] = mapped_column(Text, nullable=True)
    pollster: Mapped[str | None] = mapped_column(String(80), nullable=True)
    status: Mapped[str] = mapped_column(String(12), default="nouveau")  # nouveau/traité/ignoré
    extra: Mapped[dict] = mapped_column(JSON, default=dict)


class Indicator(Base):
    """Indicateur de fondamentaux (ex. % satisfaits du gouvernement — Léger),
    avec provenance et méthode (firecrawl / fallback-config / manuel)."""
    __tablename__ = "indicators"
    __table_args__ = (UniqueConstraint("name", "as_of", name="uq_indicator"),)
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(60), index=True)
    value: Mapped[float] = mapped_column(Float)
    as_of: Mapped[date] = mapped_column(Date)
    source: Mapped[str] = mapped_column(String(120), default="")
    source_url: Mapped[str | None] = mapped_column(Text, nullable=True)
    method: Mapped[str] = mapped_column(String(30), default="manual")
    fetched_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)
    extra: Mapped[dict] = mapped_column(JSON, default=dict)


class SocialPost(Base):
    """Publication de médias sociaux (Reddit via index Google, Mastodon, Lemmy…)
    scorée par le moteur de sentiment — signal « Pouls social », poids nul dans
    le forecast, anomalies de volume détectées."""
    __tablename__ = "social_posts"
    __table_args__ = (UniqueConstraint("url", name="uq_social_url"),
                      Index("ix_social_created", "created_at"),)
    id: Mapped[int] = mapped_column(primary_key=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime, default=utcnow)
    created_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    platform: Mapped[str] = mapped_column(String(20), index=True)  # reddit/mastodon/lemmy
    community: Mapped[str | None] = mapped_column(String(80), nullable=True)
    url: Mapped[str] = mapped_column(Text)
    author_hash: Mapped[str | None] = mapped_column(String(16), nullable=True)
    text: Mapped[str] = mapped_column(Text)
    text_hash: Mapped[str] = mapped_column(String(16), index=True)  # anti-doublon
    engagement: Mapped[float] = mapped_column(Float, default=0.0)   # votes/favs/boosts
    # {party: {"sentiment": s, "stance": st, "method": m}}
    scores: Mapped[dict] = mapped_column(JSON, default=dict)
    query: Mapped[str | None] = mapped_column(String(120), nullable=True)
    # §33 : SÉPARATION STRICTE — public_discussion (citoyens) vs official_content
    # (partis/candidats) vs media_content. Les sentiments ne se mélangent jamais :
    # le « sentiment » d'un message officiel n'est pas de l'opinion publique.
    content_class: Mapped[str] = mapped_column(String(20), default="public_discussion")


class ApifyRun(Base):
    """Observabilité des acteurs Apify (règle §34) : chaque run est journalisé;
    un échec d'acteur n'empêche jamais la publication du forecast."""
    __tablename__ = "apify_runs"
    id: Mapped[int] = mapped_column(primary_key=True)
    at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)
    actor_id: Mapped[str] = mapped_column(String(40))
    actor_name: Mapped[str] = mapped_column(String(60), default="qc-social-pulse")
    status: Mapped[str] = mapped_column(String(16))   # ok / error / timeout
    items_collected: Mapped[int] = mapped_column(Integer, default=0)
    items_valid: Mapped[int] = mapped_column(Integer, default=0)
    items_stored: Mapped[int] = mapped_column(Integer, default=0)
    runtime_s: Mapped[float | None] = mapped_column(Float, nullable=True)
    error: Mapped[str | None] = mapped_column(Text, nullable=True)
    detail: Mapped[dict] = mapped_column(JSON, default=dict)


class DataSource(Base):
    __tablename__ = "data_sources"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(80), unique=True)
    url: Mapped[str] = mapped_column(Text)
    kind: Mapped[str] = mapped_column(String(30))
    last_fetch: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    last_status: Mapped[str | None] = mapped_column(String(200), nullable=True)


class ModelVersion(Base):
    __tablename__ = "model_versions"
    id: Mapped[int] = mapped_column(primary_key=True)
    version: Mapped[str] = mapped_column(String(20), unique=True)
    released: Mapped[datetime] = mapped_column(DateTime, default=utcnow)
    changelog: Mapped[str | None] = mapped_column(Text, nullable=True)


class PipelineLog(Base):
    __tablename__ = "pipeline_logs"
    id: Mapped[int] = mapped_column(primary_key=True)
    at: Mapped[datetime] = mapped_column(DateTime, default=utcnow, index=True)
    step: Mapped[str] = mapped_column(String(40))
    status: Mapped[str] = mapped_column(String(12))  # ok / error / skip
    message: Mapped[str | None] = mapped_column(Text, nullable=True)