"""Reporting Engine models.

ReportDefinition
    A tenant-scoped report schema. Can be created by modules via the registry
    or by users through the API. Defines columns, filters, grouping, and the
    data-source resource endpoint.

ScheduledReport
    Links a ReportDefinition to a cron schedule, output format, and recipient
    list. Processed by Celery Beat via ``tick_scheduled_reports``.

KPIDefinition
    A single KPI metric — a resource URL that returns ``{value, change?, trend?}``.
    Modules register KPIs via the KPIRegistry; users can override or create
    custom KPIs.

DashboardDefinition
    A tenant-scoped dashboard layout — a JSON grid of widget placements.

ReportExecution
    Immutable append-only log of every report run (manual or scheduled).
    Stores status, row count, output file reference, and error details.
"""

from __future__ import annotations

from typing import ClassVar

from django.conf import settings
from django.db import models
from django.utils.translation import gettext_lazy as _

from simorgh.core.models import TenantScopedModel, UUIDModel
from simorgh.core.module_kit import PlatformEntityModel

# ---------------------------------------------------------------------------
# Choices
# ---------------------------------------------------------------------------

class ReportFormat(models.TextChoices):
    CSV  = "csv",  _("CSV")
    XLSX = "xlsx", _("Excel")
    PDF  = "pdf",  _("PDF")
    JSON = "json", _("JSON")


class ReportExecutionStatus(models.TextChoices):
    PENDING    = "pending",    _("Pending")
    RUNNING    = "running",    _("Running")
    COMPLETED  = "completed",  _("Completed")
    FAILED     = "failed",     _("Failed")


class KPIMetricFormat(models.TextChoices):
    NUMBER   = "number",   _("Number")
    CURRENCY = "currency", _("Currency")
    PERCENT  = "percent",  _("Percent")
    DURATION = "duration", _("Duration")


# ---------------------------------------------------------------------------
# ReportDefinition
# ---------------------------------------------------------------------------

class ReportDefinition(PlatformEntityModel):
    """A report template — schema for data, columns, filters, and grouping.

    Reports can be created:
    - Programmatically by modules via ``ReportRegistry.register()``
    - Dynamically by power users through the API

    The ``resource`` field points to the API endpoint that serves the
    report data.  ``columns`` defines which fields appear and how they
    are formatted.  ``filters_schema`` declares filterable dimensions.
    """

    code = models.CharField(
        _("code"),
        max_length=128,
        db_index=True,
        help_text=_("Unique code per tenant, e.g. 'crm.leads_by_status'"),
    )
    name = models.CharField(_("name"), max_length=255)
    description = models.TextField(_("description"), blank=True, default="")
    module = models.CharField(
        _("module"),
        max_length=64,
        blank=True,
        default="",
        db_index=True,
        help_text=_("Owning module, e.g. 'crm', 'helpdesk'"),
    )
    entity_type = models.CharField(
        _("entity type"),
        max_length=128,
        blank=True,
        default="",
        help_text=_("Dot-notation entity, e.g. 'crm.lead'"),
    )

    # Data source
    resource = models.CharField(
        _("resource URL"),
        max_length=512,
        blank=True,
        default="",
        help_text=_("API endpoint that returns report data, e.g. '/api/v1/crm/leads/report/'"),
    )

    # Schema
    columns = models.JSONField(
        _("columns"),
        default=list,
        blank=True,
        help_text=_(
            "Ordered list of column definitions: "
            '[{"key": "name", "label": "Full Name", "type": "text"}, ...]'
        ),
    )
    filters_schema = models.JSONField(
        _("filters schema"),
        default=list,
        blank=True,
        help_text=_("List of filter field definitions for the ReportFilter UI"),
    )
    parameters = models.JSONField(
        _("parameters"),
        default=dict,
        blank=True,
        help_text=_("Configurable report parameters as key-value pairs"),
    )
    group_by = models.JSONField(
        _("group by"),
        default=list,
        blank=True,
        help_text=_("List of field keys to group results by"),
    )
    sort_by = models.CharField(
        _("sort by"),
        max_length=128,
        blank=True,
        default="",
        help_text=_("Default sort field, optionally prefixed with '-' for descending"),
    )
    page_size = models.PositiveIntegerField(_("page size"), default=100)

    # Export
    export_formats = models.JSONField(
        _("export formats"),
        default=list,
        blank=True,
        help_text=_("Allowed export formats: ['csv', 'xlsx', 'pdf']"),
    )

    # State
    is_active = models.BooleanField(_("is active"), default=True, db_index=True)
    is_builtin = models.BooleanField(
        _("is built-in"),
        default=False,
        help_text=_("True for module-registered reports; False for user-created"),
    )

    class Meta:
        verbose_name = _("report definition")
        verbose_name_plural = _("report definitions")
        ordering = ("module", "name")
        constraints: ClassVar[list[models.UniqueConstraint]] = [
            models.UniqueConstraint(
                fields=("tenant", "code"),
                name="uq_report_definition_tenant_code",
            ),
        ]
        indexes: ClassVar[list[models.Index]] = [
            models.Index(fields=("tenant", "module")),
            models.Index(fields=("tenant", "is_active")),
        ]

    def __str__(self) -> str:
        return f"[{self.tenant_id}] {self.code}"

    def __repr__(self) -> str:
        return f"<ReportDefinition id={self.pk} code={self.code!r}>"


# ---------------------------------------------------------------------------
# ScheduledReport
# ---------------------------------------------------------------------------

class ScheduledReport(PlatformEntityModel):
    """A recurring report delivery schedule.

    Links a ``ReportDefinition`` to a cron expression, output format,
    and recipient list.  Celery Beat picks up due schedules via
    ``tick_scheduled_reports`` and dispatches ``execute_scheduled_report``.
    """

    report = models.ForeignKey(
        ReportDefinition,
        on_delete=models.CASCADE,
        related_name="schedules",
        verbose_name=_("report"),
    )
    cron_expression = models.CharField(
        _("cron expression"),
        max_length=128,
        help_text=_("Standard cron expression, e.g. '0 8 * * 1' for Monday 08:00"),
    )
    format = models.CharField(
        _("output format"),
        max_length=8,
        choices=ReportFormat.choices,
        default=ReportFormat.CSV,
    )
    recipients = models.JSONField(
        _("recipients"),
        default=list,
        blank=True,
        help_text=_("List of user IDs or email addresses"),
    )
    is_active = models.BooleanField(_("is active"), default=True, db_index=True)

    last_run_at = models.DateTimeField(_("last run at"), null=True, blank=True)
    next_run_at = models.DateTimeField(_("next run at"), null=True, blank=True, db_index=True)
    run_count = models.PositiveBigIntegerField(_("run count"), default=0)

    class Meta:
        verbose_name = _("scheduled report")
        verbose_name_plural = _("scheduled reports")
        ordering = ("-created_at",)
        indexes: ClassVar[list[models.Index]] = [
            models.Index(fields=("tenant", "is_active", "next_run_at")),
        ]

    def __str__(self) -> str:
        return f"[{self.tenant_id}] {self.report.code} @ {self.cron_expression}"


# ---------------------------------------------------------------------------
# KPIDefinition
# ---------------------------------------------------------------------------

class KPIDefinition(PlatformEntityModel):
    """A KPI metric definition — a single numeric indicator with optional trend.

    The ``resource`` endpoint is expected to return JSON with:
        {"value": <number>, "change": <number|null>, "trend": "up"|"down"|null}
    """

    code = models.CharField(
        _("code"),
        max_length=128,
        db_index=True,
        help_text=_("Unique code per tenant, e.g. 'crm.open_leads_count'"),
    )
    name = models.CharField(_("name"), max_length=255)
    description = models.TextField(_("description"), blank=True, default="")
    module = models.CharField(
        _("module"),
        max_length=64,
        blank=True,
        default="",
        db_index=True,
    )

    # Data source
    resource = models.CharField(
        _("resource URL"),
        max_length=512,
        help_text=_("API endpoint returning {value, change?, trend?}"),
    )
    trend_resource = models.CharField(
        _("trend resource URL"),
        max_length=512,
        blank=True,
        default="",
        help_text=_("Optional endpoint for chartable trend data"),
    )

    # Formatting
    format = models.CharField(
        _("metric format"),
        max_length=16,
        choices=KPIMetricFormat.choices,
        default=KPIMetricFormat.NUMBER,
    )
    unit = models.CharField(
        _("unit"),
        max_length=32,
        blank=True,
        default="",
        help_text=_("Unit label, e.g. 'USD', '%', 'hrs'"),
    )
    decimal_places = models.PositiveSmallIntegerField(_("decimal places"), default=0)
    threshold_warning = models.FloatField(_("warning threshold"), null=True, blank=True)
    threshold_critical = models.FloatField(_("critical threshold"), null=True, blank=True)

    is_active = models.BooleanField(_("is active"), default=True, db_index=True)
    is_builtin = models.BooleanField(
        _("is built-in"),
        default=False,
    )

    class Meta:
        verbose_name = _("KPI definition")
        verbose_name_plural = _("KPI definitions")
        ordering = ("module", "name")
        constraints: ClassVar[list[models.UniqueConstraint]] = [
            models.UniqueConstraint(
                fields=("tenant", "code"),
                name="uq_kpi_definition_tenant_code",
            ),
        ]
        indexes: ClassVar[list[models.Index]] = [
            models.Index(fields=("tenant", "module")),
            models.Index(fields=("tenant", "is_active")),
        ]

    def __str__(self) -> str:
        return f"[{self.tenant_id}] {self.code}"


# ---------------------------------------------------------------------------
# DashboardDefinition
# ---------------------------------------------------------------------------

class DashboardDefinition(PlatformEntityModel):
    """A dashboard layout — grid of widget placements.

    The ``layout`` JSON defines rows and columns of widgets:

        {
          "rows": [
            {"columns": [
              {"widget": "kpi", "kpi_code": "crm.open_leads_count", "col_span": 1},
              {"widget": "chart", "resource": "/api/v1/crm/leads/trend/", "chart_type": "line", "col_span": 3},
            ]}
          ]
        }
    """

    code = models.CharField(
        _("code"),
        max_length=128,
        db_index=True,
        help_text=_("Unique code per tenant, e.g. 'crm.sales_dashboard'"),
    )
    name = models.CharField(_("name"), max_length=255)
    description = models.TextField(_("description"), blank=True, default="")
    module = models.CharField(
        _("module"),
        max_length=64,
        blank=True,
        default="",
        db_index=True,
    )
    layout = models.JSONField(
        _("layout"),
        default=dict,
        blank=True,
        help_text=_("Grid layout with widget placements as JSON"),
    )
    is_active = models.BooleanField(_("is active"), default=True, db_index=True)
    is_default = models.BooleanField(
        _("is default"),
        default=False,
        help_text=_("True if this is the default dashboard for the tenant"),
    )
    is_builtin = models.BooleanField(
        _("is built-in"),
        default=False,
    )

    class Meta:
        verbose_name = _("dashboard definition")
        verbose_name_plural = _("dashboard definitions")
        ordering = ("-is_default", "module", "name")
        constraints: ClassVar[list[models.UniqueConstraint]] = [
            models.UniqueConstraint(
                fields=("tenant", "code"),
                name="uq_dashboard_definition_tenant_code",
            ),
        ]
        indexes: ClassVar[list[models.Index]] = [
            models.Index(fields=("tenant", "module")),
            models.Index(fields=("tenant", "is_default")),
        ]

    def __str__(self) -> str:
        return f"[{self.tenant_id}] {self.code}"


# ---------------------------------------------------------------------------
# ReportExecution
# ---------------------------------------------------------------------------

class ReportExecution(UUIDModel, TenantScopedModel):
    """Append-only log of a single report run.

    One row per execution — whether triggered manually, via schedule,
    or via the export pipeline.
    """

    report = models.ForeignKey(
        ReportDefinition,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="executions",
        verbose_name=_("report"),
    )
    schedule = models.ForeignKey(
        ScheduledReport,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="executions",
        verbose_name=_("schedule"),
    )

    status = models.CharField(
        _("status"),
        max_length=16,
        choices=ReportExecutionStatus.choices,
        default=ReportExecutionStatus.PENDING,
        db_index=True,
    )
    format = models.CharField(
        _("output format"),
        max_length=8,
        choices=ReportFormat.choices,
        blank=True,
        default="",
    )
    parameters = models.JSONField(
        _("parameters"),
        default=dict,
        blank=True,
    )
    filters_applied = models.JSONField(
        _("filters applied"),
        default=dict,
        blank=True,
    )

    # Output
    file = models.ForeignKey(
        "storage.FileMetadata",
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="+",
        verbose_name=_("output file"),
    )
    row_count = models.PositiveIntegerField(_("row count"), null=True, blank=True)
    error_message = models.TextField(_("error message"), blank=True, default="")

    # Timing
    started_at = models.DateTimeField(_("started at"), null=True, blank=True)
    finished_at = models.DateTimeField(_("finished at"), null=True, blank=True)
    duration_seconds = models.FloatField(_("duration seconds"), null=True, blank=True)

    # Actor
    triggered_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        related_name="+",
        verbose_name=_("triggered by"),
    )

    class Meta:
        verbose_name = _("report execution")
        verbose_name_plural = _("report executions")
        ordering = ("-started_at",)
        indexes: ClassVar[list[models.Index]] = [
            models.Index(fields=("tenant", "report")),
            models.Index(fields=("tenant", "schedule")),
            models.Index(fields=("tenant", "status")),
        ]
        get_latest_by = "started_at"

    def __str__(self) -> str:
        report_code = self.report.code if self.report else "?"
        return f"Execution of {report_code} [{self.status}]"

    def mark_started(self) -> None:
        """Transition to RUNNING and record start time."""
        from django.utils import timezone

        self.status = ReportExecutionStatus.RUNNING
        self.started_at = timezone.now()
        self.save(update_fields=["status", "started_at"])

    def mark_completed(self, *, row_count: int, file_id: int | None = None) -> None:
        """Transition to COMPLETED and record output metadata."""
        from django.utils import timezone

        now = timezone.now()
        self.status = ReportExecutionStatus.COMPLETED
        self.finished_at = now
        self.row_count = row_count
        if file_id:
            self.file_id = file_id
        if self.started_at:
            self.duration_seconds = (now - self.started_at).total_seconds()
        self.save(
            update_fields=[
                "status", "finished_at", "row_count",
                "file_id", "duration_seconds",
            ]
        )

    def mark_failed(self, *, error_message: str) -> None:
        """Transition to FAILED and record the error."""
        from django.utils import timezone

        now = timezone.now()
        self.status = ReportExecutionStatus.FAILED
        self.finished_at = now
        self.error_message = error_message
        if self.started_at:
            self.duration_seconds = (now - self.started_at).total_seconds()
        self.save(
            update_fields=[
                "status", "finished_at", "error_message", "duration_seconds",
            ]
        )