import re

from sqlalchemy import collate
from sqlalchemy.sql import text

from registry.extensions import db
from registry.list.models import DonationCenter, Medals
from registry.utils import (
    EMAIL_RE,
    capitalize,
    format_postal_code,
    split_degrees,
)


class Batch(db.Model):
    __tablename__ = "batches"
    id = db.Column(db.Integer, primary_key=True)
    donation_center_id = db.Column(db.ForeignKey(DonationCenter.id))
    donation_center = db.relationship("DonationCenter")
    imported_at = db.Column(db.DateTime, nullable=False)

    def __repr__(self):
        return f"<Batch({self.id}) from {self.imported_at}>"


class Record(db.Model):
    __tablename__ = "records"
    id = db.Column(db.Integer, primary_key=True)
    batch_id = db.Column(db.ForeignKey(Batch.id, ondelete="CASCADE"), nullable=False)
    batch = db.relationship("Batch")
    rodne_cislo = db.Column(db.String(10), index=True, nullable=False)
    first_name = db.Column(db.String, nullable=False)
    last_name = db.Column(db.String, nullable=False)
    address = db.Column(db.String, nullable=False)
    city = db.Column(db.String, nullable=False)
    postal_code = db.Column(db.String(5), nullable=False)
    kod_pojistovny = db.Column(db.String(3), nullable=False)
    donation_count = db.Column(db.Integer, nullable=False)

    def __repr__(self):
        return f"<Record({self.id}) {self.rodne_cislo} from Batch {self.batch}>"

    @classmethod
    def from_list(cls, list):
        return cls(
            batch_id=list[0],
            rodne_cislo=list[1],
            first_name=list[2],
            last_name=list[3],
            address=list[4],
            city=list[5],
            postal_code=list[6],
            kod_pojistovny=list[7],
            donation_count=list[8],
        )


class IgnoredDonors(db.Model):
    __tablename__ = "ignored_donors"
    rodne_cislo = db.Column(db.String(10), primary_key=True)
    reason = db.Column(db.String, nullable=False)
    ignored_since = db.Column(db.DateTime, nullable=False)


class AwardedMedals(db.Model):
    __tablename__ = "awarded_medals"
    rodne_cislo = db.Column(db.String(10), index=True, nullable=False)
    medal_id = db.Column(db.ForeignKey(Medals.id))
    medal = db.relationship("Medals")
    # NULL means unknown data - imported from the old system
    awarded_at = db.Column(db.DateTime, nullable=True)
    __tableargs__ = (db.PrimaryKeyConstraint(rodne_cislo, medal_id),)


class AwardEligibilitySnapshot(db.Model):
    """Stores eligible donors for annual medal awards.

    For higher-value medals (gold and above), we need to know who was eligible
    based on donations from the previous year. This table stores a snapshot of
    eligible rodne_cislo values for a given medal and year.
    """

    __tablename__ = "award_eligibility_snapshots"
    medal_id = db.Column(db.ForeignKey(Medals.id), nullable=False)
    medal = db.relationship("Medals")
    year = db.Column(db.Integer, nullable=False)
    rodne_cislo = db.Column(db.String(10), nullable=False)
    created_at = db.Column(db.DateTime, nullable=False)
    __table_args__ = (db.PrimaryKeyConstraint("medal_id", "year", "rodne_cislo"),)

    @classmethod
    def create_snapshot(cls, medal, year):
        """Create a snapshot of eligible donors for the given medal and year.

        The snapshot uses donation counts as of the creation time (now).
        Only checks donors who are currently eligible (not already awarded),
        then verifies they were also eligible on the cutoff date.

        Args:
            medal: Medals object
            year: Year for which awards are being prepared (e.g., 2026)

        Returns:
            Number of eligible donors found
        """
        from datetime import datetime

        from sqlalchemy import and_

        # Check if snapshot already exists for this medal/year
        existing = cls.query.filter_by(medal_id=medal.id, year=year).first()
        if existing:
            # Snapshot already created, return count (excluding marker)
            all_entries = cls.query.filter_by(medal_id=medal.id, year=year).all()
            return len([e for e in all_entries if e.rodne_cislo != "__EMPTY__"])

        # Cutoff date is now (when snapshot is created)
        cutoff_datetime = datetime.now()
        cutoff_date = cutoff_datetime.strftime("%Y-%m-%d %H:%M:%S")

        # First, get currently eligible donors who haven't been awarded (optimization)
        currently_eligible = DonorsOverview.query.filter(
            and_(
                DonorsOverview.donation_count_total >= medal.minimum_donations,
                getattr(DonorsOverview, "awarded_medal_" + medal.slug).is_(False),
            )
        ).all()

        candidate_rodne_cisla = [d.rodne_cislo for d in currently_eligible]

        if not candidate_rodne_cisla:
            # No current candidates - insert marker and return 0
            marker = cls(
                medal_id=medal.id,
                year=year,
                rodne_cislo="__EMPTY__",
                created_at=cutoff_datetime,
            )
            db.session.add(marker)
            db.session.commit()
            return 0

        # Build IN clause with proper placeholders for SQLite
        placeholders = ",".join([f":rc_{i}" for i in range(len(candidate_rodne_cisla))])

        # Query to calculate historical donation counts for candidates
        # Uses the same logic as donors_overview but with date filter
        query = text(
            f"""
            SELECT "rodne_cislo"
            FROM (
                SELECT
                    "outer_records"."rodne_cislo",
                    (
                        SELECT SUM("donation_count"."donation_count")
                        FROM (
                            SELECT (
                                SELECT "records"."donation_count"
                                FROM "records"
                                    JOIN "batches"
                                        ON "batches"."id" = "records"."batch_id"
                                WHERE "records"."rodne_cislo" = "outer_records"."rodne_cislo"
                                    AND "batches"."imported_at" <= :cutoff_date
                                    AND (
                                        "batches"."donation_center_id" = "donation_center_null"."donation_center_id"
                                        OR (
                                            "batches"."donation_center_id" IS NULL AND
                                            "donation_center_null"."donation_center_id" IS NULL
                                        )
                                    )
                                ORDER BY "batches"."imported_at" DESC,
                                    "records"."donation_count" DESC
                                LIMIT 1
                            ) AS "donation_count"
                            FROM (
                                SELECT "donation_centers"."id" AS "donation_center_id"
                                FROM "donation_centers"
                                UNION
                                SELECT NULL AS "donation_centers"
                            ) AS "donation_center_null"
                            WHERE "donation_count" IS NOT NULL
                        ) AS "donation_count"
                    ) AS historical_total
                FROM (
                    SELECT DISTINCT "rodne_cislo"
                    FROM "records"
                    WHERE "rodne_cislo" IN ({placeholders})
                ) AS "outer_records"
            ) AS results
            WHERE historical_total >= :minimum_donations
        """
        )

        # Build parameters dict with individual rodne_cislo parameters
        params = {
            "cutoff_date": cutoff_date,
            "minimum_donations": medal.minimum_donations,
        }
        for i, rc in enumerate(candidate_rodne_cisla):
            params[f"rc_{i}"] = rc

        result = db.session.execute(query, params)

        eligible_rodne_cisla = [row[0] for row in result]

        # Store the snapshot
        for rodne_cislo in eligible_rodne_cisla:
            snapshot = cls(
                medal_id=medal.id,
                year=year,
                rodne_cislo=rodne_cislo,
                created_at=cutoff_datetime,
            )
            db.session.add(snapshot)

        db.session.commit()
        return len(eligible_rodne_cisla)

    @classmethod
    def get_eligible_rodne_cisla(cls, medal_id, year):
        """Get list of eligible rodne_cislo for a medal and year.

        Returns:
            List of rodne_cislo strings, or None if snapshot doesn't exist
        """
        # Check if snapshot was ever created (even if empty)
        snapshot_exists = (
            cls.query.filter_by(medal_id=medal_id, year=year).first() is not None
        )

        if not snapshot_exists:
            return None

        # Return all eligible rodne_cisla (excluding marker for empty snapshots)
        snapshots = cls.query.filter_by(medal_id=medal_id, year=year).all()
        return [s.rodne_cislo for s in snapshots if s.rodne_cislo != "__EMPTY__"]


class DonorsOverview(db.Model):
    __tablename__ = "donors_overview"
    rodne_cislo = db.Column(db.String(10), primary_key=True)
    first_name = db.Column(db.String, nullable=False)
    last_name = db.Column(db.String, nullable=False)
    address = db.Column(db.String, nullable=False)
    city = db.Column(db.String, nullable=False)
    postal_code = db.Column(db.String(5), nullable=False)
    kod_pojistovny = db.Column(db.String(3), nullable=False)
    donation_count_fm = db.Column(db.Integer, nullable=False)
    donation_count_fm_bubenik = db.Column(db.Integer, nullable=False)
    donation_count_trinec = db.Column(db.Integer, nullable=False)
    donation_count_mp = db.Column(db.Integer, nullable=False)
    donation_count_manual = db.Column(db.Integer, nullable=False)
    donation_count_total = db.Column(db.Integer, nullable=False)
    awarded_medal_br = db.Column(db.Boolean, nullable=False)
    awarded_medal_st = db.Column(db.Boolean, nullable=False)
    awarded_medal_zl = db.Column(db.Boolean, nullable=False)
    awarded_medal_kr3 = db.Column(db.Boolean, nullable=False)
    awarded_medal_kr2 = db.Column(db.Boolean, nullable=False)
    awarded_medal_kr1 = db.Column(db.Boolean, nullable=False)
    awarded_medal_plk = db.Column(db.Boolean, nullable=False)
    note = db.relationship(
        "Note",
        uselist=False,
        primaryjoin="foreign(DonorsOverview.rodne_cislo) == Note.rodne_cislo",
    )

    frontend_column_names = {
        "rodne_cislo": "Rodné číslo",
        "first_name": "Jméno",
        "last_name": "Příjmení",
        "address": "Adresa",
        "city": "Město",
        "postal_code": "PSČ",
        "kod_pojistovny": "Pojišťovna",
        "donations": "Darování Celkem",
        "last_award": "Ocenění",
        "note": "Pozn.",
    }

    # Fields for frontend not calculated from multiple columns
    basic_fields = [
        c
        for c in frontend_column_names.keys()
        if c not in ("donations", "last_award", "note")
    ]

    def __repr__(self):
        return f"<DonorsOverview ({self.rodne_cislo})>"

    @classmethod
    def get_filter_for_search(cls, search_str):
        conditions_all = []
        for part in search_str.split():
            conditions_all.append([])
            for column_name in cls.frontend_column_names.keys():
                if hasattr(cls, column_name):
                    if column_name == "note":
                        column = Note.note
                    else:
                        column = getattr(cls, column_name)
                    contains = getattr(column, "contains")
                    conditions_all[-1].append(contains(part, autoescape=True))
        return db.and_(*[db.or_(*conditions) for conditions in conditions_all])

    @classmethod
    def get_order_by_for_column_id(cls, column_id, direction):
        column_name = list(cls.frontend_column_names.keys())[column_id]
        if hasattr(cls, column_name):
            column = getattr(cls, column_name)
            column_with_collation = collate(column, "czech")
            return (getattr(column_with_collation, direction)(),)
        elif column_name == "donations":
            column_name = "donation_count_total"
            column = getattr(cls, column_name)
            return (getattr(column, direction)(),)
        elif column_name == "last_award":
            order_by = []
            for medal in Medals.query.order_by(Medals.id.asc()).all():
                column = getattr(cls, "awarded_medal_" + medal.slug)
                order_by.append(getattr(column, direction)())
            return order_by

    def dict_for_frontend(self):
        # All standard attributes
        donor_dict = {}
        for name in self.frontend_column_names.keys():
            donor_dict[name] = getattr(self, name, None)
            # Note is special because note column contains
            # Note object but we need to get its text which
            # is in Note.note attr. We send it as structured data
            # so the frontend can display different icons for different types.
            if donor_dict[name] is not None and name == "note":
                note_obj = donor_dict[name]
                emails = note_obj.get_emails_from_note()
                phones = note_obj.get_phones_from_note()

                # Calculate "other text" by removing emails and phones
                other_text = note_obj.note
                for email in emails:
                    other_text = other_text.replace(email, "")
                for phone in phones:
                    other_text = other_text.replace(phone, "")
                # Clean up whitespace
                other_text = "\n".join(
                    line.strip() for line in other_text.split("\n") if line.strip()
                )

                donor_dict[name] = {
                    "emails": emails,
                    "phones": phones,
                    "other": other_text,
                    "raw": note_obj.note,  # Keep full text for fallback
                }
            elif donor_dict[name] is not None and name in (
                "first_name",
                "last_name",
                "address",
                "city",
            ):
                donor_dict[name] = capitalize(donor_dict[name])

        # Highest awarded medal
        for medal in Medals.query.order_by(Medals.id.desc()).all():
            if getattr(self, "awarded_medal_" + medal.slug):
                donor_dict["last_award"] = medal.title
                break
            else:
                donor_dict["last_award"] = "Žádné"
        # Dict with all donations which we use on frontend
        # to generate tooltip
        donor_dict["donations"] = {
            dc.slug: {
                "count": getattr(self, "donation_count_" + dc.slug),
                "name": dc.title,
            }
            for dc in DonationCenter.query.all()
        }
        donor_dict["donations"]["manual"] = {
            "count": self.donation_count_manual,
            "name": "Jinde",
        }
        donor_dict["donations"]["total"] = self.donation_count_total

        # Format the donor's postal code
        donor_dict["postal_code"] = format_postal_code(self.postal_code)
        return donor_dict

    @classmethod
    def refresh_overview(cls, rodne_cislo=None):
        if rodne_cislo:
            row = db.session.get(cls, rodne_cislo)
            if row is not None:
                db.session.delete(row)
            record = Record.query.filter(
                Record.rodne_cislo == rodne_cislo
            ).first_or_404()
            # Thanks to the lines above, we know that it's safe to create this small
            # part of the SQL query manually. Usually it's a bad idea due to possible
            # SQL injection, but, we know that rodne_cislo is valid and exists in
            # other parts of this database so it should be fine to use it like this.
            sql_condition = "records.rodne_cislo = :rodne_cislo AND "
            params = {"rodne_cislo": record.rodne_cislo}
        else:
            cls.query.delete()
            sql_condition = ""
            params = {}
        db.session.commit()
        full_query = f"""INSERT INTO "donors_overview"
    (
        "rodne_cislo",
        "first_name",
        "last_name",
        "address",
        "city",
        "postal_code",
        "kod_pojistovny",
        "donation_count_fm",
        "donation_count_fm_bubenik",
        "donation_count_trinec",
        "donation_count_mp",
        "donation_count_manual",
        "donation_count_total",
        "awarded_medal_br",
        "awarded_medal_st",
        "awarded_medal_zl",
        "awarded_medal_kr3",
        "awarded_medal_kr2",
        "awarded_medal_kr1",
        "awarded_medal_plk"
    )
SELECT
    -- "rodne_cislo" uniquely identifies a person.
    "records"."rodne_cislo",
    -- Personal data from the person’s most recent batch
    -- or from manual overrides.
    COALESCE(
        "donors_override"."first_name",
        "records"."first_name"
    ),
    COALESCE(
        "donors_override"."last_name",
        "records"."last_name"
    ),
    COALESCE(
        "donors_override"."address",
        "records"."address"
    ),
    COALESCE(
        "donors_override"."city",
        "records"."city"
    ),
    COALESCE(
        "donors_override"."postal_code",
        "records"."postal_code"
    ),
    COALESCE(
        "donors_override"."kod_pojistovny",
        "records"."kod_pojistovny"
    ),
    -- Total donation counts for each donation center. The value in
    -- a record is incremental. Thus retrieving the one from the most
    -- recent batch that belongs to the donation center. Coalescing to
    -- 0 for cases when there is no record from the donation center.
    COALESCE(
        (
            SELECT "records"."donation_count"
            FROM "records"
                 JOIN "batches"
                      ON "batches"."id" = "records"."batch_id"
                 JOIN "donation_centers"
                      ON "donation_centers"."id" = "batches"."donation_center_id"
            WHERE "records"."rodne_cislo" = "recent_records"."rodne_cislo"
                AND "donation_centers"."slug" = 'fm'
            ORDER BY "batches"."imported_at" DESC,
                "records"."donation_count" DESC
            LIMIT 1
        ),
        0
     ) AS "donation_count_fm",
    COALESCE(
        (
            SELECT "records"."donation_count"
            FROM "records"
                 JOIN "batches"
                      ON "batches"."id" = "records"."batch_id"
                 JOIN "donation_centers"
                      ON "donation_centers"."id" = "batches"."donation_center_id"
            WHERE "records"."rodne_cislo" = "recent_records"."rodne_cislo"
                AND "donation_centers"."slug" = 'fm_bubenik'
            ORDER BY "batches"."imported_at" DESC,
                "records"."donation_count" DESC
            LIMIT 1
        ),
        0
    ) AS "donation_count_fm_bubenik",
    COALESCE(
        (
            SELECT "records"."donation_count"
            FROM "records"
                 JOIN "batches"
                      ON "batches"."id" = "records"."batch_id"
                 JOIN "donation_centers"
                      ON "donation_centers"."id" = "batches"."donation_center_id"
            WHERE "records"."rodne_cislo" = "recent_records"."rodne_cislo"
                AND "donation_centers"."slug" = 'trinec'
            ORDER BY "batches"."imported_at" DESC,
                "records"."donation_count" DESC
            LIMIT 1
        ),
        0
    ) AS "donation_count_trinec",
    COALESCE(
        (
            SELECT "records"."donation_count"
            FROM "records"
                 JOIN "batches"
                      ON "batches"."id" = "records"."batch_id"
                 JOIN "donation_centers"
                      ON "donation_centers"."id" = "batches"."donation_center_id"
            WHERE "records"."rodne_cislo" = "recent_records"."rodne_cislo"
                AND "donation_centers"."slug" = 'mp'
            ORDER BY "batches"."imported_at" DESC,
                "records"."donation_count" DESC
            LIMIT 1
        ),
        0
    ) AS "donation_count_mp",
    COALESCE(
        (
            SELECT "records"."donation_count"
            FROM "records"
                 JOIN "batches"
                      ON "batches"."id" = "records"."batch_id"
            WHERE "records"."rodne_cislo" = "recent_records"."rodne_cislo"
                AND "batches"."donation_center_id" IS NULL
            ORDER BY "batches"."imported_at" DESC,
                "records"."donation_count" DESC
            LIMIT 1
        ),
        0
    ) AS "donation_count_manual",
    -- The grand total of the donation counts. Sums the most recent
    -- counts from all the donation centers and the most recent manual
    -- donation count without a donation center. Not coalescing this
    -- one, because it is not possible for a person not no have any
    -- donation record at all.
    (
        -- Sum all the respective donation counts including manual
        -- entries.
        SELECT SUM("donation_count"."donation_count")
        FROM (
            SELECT (
                -- Loads the most recent donation count for the
                -- donation center.
                SELECT "records"."donation_count"
                FROM "records"
                    JOIN "batches"
                        ON "batches"."id" = "records"."batch_id"
                WHERE "records"."rodne_cislo" = "recent_records"."rodne_cislo"
                    AND (
                        -- NULL values represent manual entries and
                        -- cannot be compared by =.
                        "batches"."donation_center_id" =
                            "donation_center_null"."donation_center_id"
                        OR (
                            "batches"."donation_center_id" IS NULL AND
                            "donation_center_null"."donation_center_id" IS NULL
                        )
                    )
                ORDER BY "batches"."imported_at" DESC,
                    "records"."donation_count" DESC
                LIMIT 1
            ) AS "donation_count"
            FROM (
                -- All possible donation centers including NULL
                -- for manual entries.
                SELECT "donation_centers"."id" AS "donation_center_id"
                FROM "donation_centers"
                UNION
                SELECT NULL AS "donation_centers"
            ) AS "donation_center_null"
            -- Removes donation centers from which the person does
            -- not have any records. This removes the need for
            -- coalescing the value to 0 before summing.
            WHERE "donation_count" IS NOT NULL
        ) AS "donation_count"
    ) AS "donation_count_total",
    -- Awarded medals checks. Just simply query whether there is a
    -- record for the given combination of "rodne_cislo" and "medal".
    EXISTS(
        SELECT 1
        FROM "awarded_medals"
            JOIN "medals"
                ON "medals"."id" = "awarded_medals"."medal_id"
        WHERE "awarded_medals"."rodne_cislo" = "records"."rodne_cislo"
            AND "medals"."slug" = 'br'
    ) AS "awarded_medal_br",
    EXISTS(
        SELECT 1
        FROM "awarded_medals"
            JOIN "medals"
                ON "medals"."id" = "awarded_medals"."medal_id"
        WHERE "awarded_medals"."rodne_cislo" = "records"."rodne_cislo"
            AND "medals"."slug" = 'st'
    ) AS "awarded_medal_st",
    EXISTS(
        SELECT 1
        FROM "awarded_medals"
            JOIN "medals"
                ON "medals"."id" = "awarded_medals"."medal_id"
        WHERE "awarded_medals"."rodne_cislo" = "records"."rodne_cislo"
            AND "medals"."slug" = 'zl'
    ) AS "awarded_medal_zl",
    EXISTS(
        SELECT 1
        FROM "awarded_medals"
            JOIN "medals"
                ON "medals"."id" = "awarded_medals"."medal_id"
        WHERE "awarded_medals"."rodne_cislo" = "records"."rodne_cislo"
            AND "medals"."slug" = 'kr3'
    ) AS "awarded_medal_kr3",
    EXISTS(
        SELECT 1
        FROM "awarded_medals"
            JOIN "medals"
                ON "medals"."id" = "awarded_medals"."medal_id"
        WHERE "awarded_medals"."rodne_cislo" = "records"."rodne_cislo"
            AND "medals"."slug" = 'kr2'
    ) AS "awarded_medal_kr2",
    EXISTS(
        SELECT 1
        FROM "awarded_medals"
            JOIN "medals"
                ON "medals"."id" = "awarded_medals"."medal_id"
        WHERE "awarded_medals"."rodne_cislo" = "records"."rodne_cislo"
            AND "medals"."slug" = 'kr1'
    ) AS "awarded_medal_kr1",
    EXISTS(
        SELECT 1
        FROM "awarded_medals"
            JOIN "medals"
                ON "medals"."id" = "awarded_medals"."medal_id"
        WHERE "awarded_medals"."rodne_cislo" = "records"."rodne_cislo"
            AND "medals"."slug" = 'plk'
    ) AS "awarded_medal_plk"
FROM (
    SELECT
        "rodna_cisla"."rodne_cislo",
        (
            -- Looks up the most recently imported batch for a given
            -- person, regardless of the donation center. This is used
            -- only to link the most recent personal data as the
            -- combination of "rodne_cislo" and "batch" is unique.
            SELECT "records"."id"
            FROM "records"
                JOIN "batches"
                    ON "batches"."id" = "records"."batch_id"
            WHERE "records"."rodne_cislo" = "rodna_cisla"."rodne_cislo"
            ORDER BY "batches"."imported_at" DESC,
                "records"."donation_count" DESC
            LIMIT 1
        ) AS "record_id"
    FROM (
        -- The ultimate core. We need all people, not records or
        -- batches. People are uniquely identified by their
        -- "rodne_cislo".
        SELECT DISTINCT "rodne_cislo"
        FROM "records"
        WHERE {sql_condition} "records"."rodne_cislo" NOT IN (
            SELECT "rodne_cislo" FROM "ignored_donors"
        )
    ) AS "rodna_cisla"
) AS "recent_records"
    JOIN "records"
        ON "records"."id" = "recent_records"."record_id"
    LEFT JOIN "donors_override"
        ON "donors_override"."rodne_cislo" = "records"."rodne_cislo";
"""  # nosec

        db.session.execute(text(full_query), params)
        db.session.commit()

        # Code moving degrees from last_name to first_name.
        if rodne_cislo:
            donors_with_degrees = (db.session.get(DonorsOverview, rodne_cislo),)
        else:
            # Because only 4 % of donors have a degree, it makes
            # sense to pre-select them via this query.
            donors_with_degrees = DonorsOverview.query.filter(
                db.or_(
                    DonorsOverview.last_name.contains(" "),
                    DonorsOverview.last_name.contains("."),
                    DonorsOverview.last_name.contains(","),
                )
            ).all()

        for donor in donors_with_degrees:
            last_name, degrees = split_degrees(donor.last_name)
            if degrees:
                donor.first_name = degrees + " " + donor.first_name
                donor.last_name = last_name
                db.session.add(donor)

        db.session.commit()


class Note(db.Model):
    __tablename__ = "notes"
    rodne_cislo = db.Column(db.String(10), primary_key=True)
    note = db.Column(db.Text)

    def __repr__(self):
        return f"<Note for {self.rodne_cislo}: {self.note}>"

    def get_emails_from_note(self):
        return re.findall(EMAIL_RE, self.note)

    def get_phones_from_note(self):
        """Extract all phone numbers from note."""
        from registry.utils import PHONE_RE

        return re.findall(PHONE_RE, self.note)

    def get_all_contacts(self):
        """Get structured contact information from note."""
        return {
            "emails": self.get_emails_from_note(),
            "phones": self.get_phones_from_note(),
        }


class DonorsOverride(db.Model):
    __tablename__ = "donors_override"
    rodne_cislo = db.Column(db.String(10), primary_key=True)
    first_name = db.Column(db.String)
    last_name = db.Column(db.String)
    address = db.Column(db.String)
    city = db.Column(db.String)
    postal_code = db.Column(db.String(5))
    kod_pojistovny = db.Column(db.String(3))

    def to_dict(self):
        result = {}
        for field in DonorsOverview.basic_fields:
            if getattr(self, field) is not None:
                result[field] = str(getattr(self, field))
            else:
                result[field] = None

        return result


class ContactImportLog(db.Model):
    """Audit log for contact imports."""

    __tablename__ = "contact_import_logs"
    id = db.Column(db.Integer, primary_key=True)
    imported_at = db.Column(db.DateTime, nullable=False)
    imported_by_user_id = db.Column(
        db.Integer, db.ForeignKey("users.id"), nullable=False
    )
    imported_by_user = db.relationship("User")
    filename = db.Column(db.String, nullable=True)  # Null if pasted directly
    input_data = db.Column(db.Text, nullable=False)
    processed_lines_count = db.Column(db.Integer, nullable=False)
    created_notes_count = db.Column(db.Integer, nullable=False, default=0)
    updated_notes_count = db.Column(db.Integer, nullable=False, default=0)
    emails_added_count = db.Column(db.Integer, nullable=False, default=0)
    phones_added_count = db.Column(db.Integer, nullable=False, default=0)

    def __repr__(self):
        return f"<ContactImportLog({self.id}) at {self.imported_at}>"
