"""Produce a de-identified COPY of a portal database for demos, testing or sharing.

The live database is never modified. On the copy:

* Bank / lender / broker company names, investor names -> invented institution names
  (consistent everywhere, so joins and leaderboards still work).
* Broker NMLS IDs, street addresses, ZIP codes, phone numbers -> removed or synthetic.
* Borrower, account executive, underwriter and processor names -> invented names.
* Loan numbers -> OBF-00001 style keys (originals can identify a file in the source system).
* Company name setting -> "Sample Mortgage Co."
* Users, sign-in history, audit events, import provenance and free-text loan event
  details are emptied (they can contain any of the above).

A final scan checks that no original name or identifier survives anywhere in the copy.
Business figures (amounts, rates, dates) are kept; whether those may be shared is a
policy decision for the data owner.

Mappings use HMAC-SHA256 with a key that is random per run unless
MORTGAGE_OBFUSCATION_KEY is set (set it only if two copies must map identically).
"""
import hashlib
import hmac
import os
import secrets
import sqlite3

PREFIX = ['Granite', 'Harbor', 'Juniper', 'Copper', 'Silver', 'Meadow', 'Pioneer', 'Redwood', 'Sterling',
          'Willow', 'Beacon', 'Crescent', 'Falcon', 'Highland', 'Ironwood', 'Keystone', 'Lakeside', 'Maple',
          'Northgate', 'Oakmont', 'Prairie', 'Quarry', 'Riverbend', 'Sagebrush', 'Timber', 'Upland', 'Valley',
          'Westwind', 'Yellowstone', 'Aspen', 'Bluff', 'Canyon']
SUFFIX = ['Bay', 'Crest', 'Point', 'Ridge', 'Springs', 'Trail', 'Park', 'Hollow', 'Gate', 'Field']
BROKER_TYPE = ['Lending', 'Mortgage', 'Home Loans', 'Funding', 'Financial', 'Mortgage Group']
INVESTOR_TYPE = ['Bank', 'Bancorp', 'Capital Markets', 'Savings Bank', 'Trust Bank', 'Financial']
FIRST = ['Alex', 'Blair', 'Cameron', 'Drew', 'Emerson', 'Finley', 'Gray', 'Hayden', 'Indy', 'Jules', 'Kai',
         'Logan', 'Marlowe', 'Noel', 'Oakley', 'Peyton', 'Quinn', 'Remy', 'Sage', 'Tatum', 'Umi', 'Vale',
         'Wren', 'Yael', 'Zion', 'Arden', 'Briar', 'Cove', 'Dale', 'Ellis']
LAST = ['Ashford', 'Bramwell', 'Calloway', 'Dunmore', 'Ellison', 'Fairbanks', 'Galloway', 'Hartwell',
        'Inglewood', 'Jessup', 'Kingsley', 'Lockhart', 'Merriweather', 'Northcott', 'Oakridge', 'Prescott',
        'Quimby', 'Radcliffe', 'Sutherland', 'Thornton', 'Underwood', 'Vance', 'Whitlock', 'Yardley']
PEOPLE_COLUMNS = ('account_executive', 'underwriter', 'processor')


class Mapper:
    def __init__(self, key=None):
        self.key = (key or os.environ.get('MORTGAGE_OBFUSCATION_KEY') or secrets.token_hex(32)).encode()
        self.maps, self.used = {}, {}

    def _digest(self, kind, value):
        return int(hmac.new(self.key, f'{kind}\x00{value.strip().lower()}'.encode(), hashlib.sha256).hexdigest(), 16)

    def _unique(self, kind, value, make):
        """Same input -> same output; different inputs never collide within a kind."""
        if value is None or not str(value).strip():
            return value
        table = self.maps.setdefault(kind, {})
        norm = str(value).strip().lower()
        if norm not in table:
            used = self.used.setdefault(kind, set())
            n, attempt = self._digest(kind, norm), 0
            candidate = make(n, attempt)
            while candidate.lower() in used:
                attempt += 1
                candidate = make(n, attempt)
            used.add(candidate.lower())
            table[norm] = candidate
        return table[norm]

    @staticmethod
    def _place(n, attempt):
        name = f'{PREFIX[n % len(PREFIX)]} {SUFFIX[(n // 97) % len(SUFFIX)]}'
        return name if attempt == 0 else f'{name} {attempt + 1}'

    def institution(self, value):
        """Brokers and investors share one map so a lender that is both stays consistent."""
        def make(n, attempt):
            return f'{self._place(n, attempt)} {BROKER_TYPE[(n // 7) % len(BROKER_TYPE)]}'
        return self._unique('institution', value, make)

    def investor(self, value):
        def make(n, attempt):
            return f'{self._place(n, attempt)} {INVESTOR_TYPE[(n // 11) % len(INVESTOR_TYPE)]}'
        existing = self.maps.get('institution', {}).get(str(value or '').strip().lower())
        return existing or self._unique('institution', value, make)

    def person(self, value):
        def make(n, attempt):
            name = f'{FIRST[n % len(FIRST)]} {LAST[(n // 31) % len(LAST)]}'
            return name if attempt == 0 else f'{name} {attempt + 1}'
        return self._unique('person', value, make)

    def first(self, value):
        return self._unique('first', value, lambda n, a: FIRST[(n + a) % len(FIRST)] + ('' if a < len(FIRST) else str(a)))

    def last(self, value):
        return self._unique('last', value, lambda n, a: LAST[(n + a) % len(LAST)] + ('' if a < len(LAST) else str(a)))


def _sensitive_values(conn):
    """Every original identifying string, used to prove nothing survived."""
    values = set()
    queries = [
        'SELECT broker_company FROM loans', 'SELECT investor_name FROM loans', 'SELECT loan_number FROM loans',
        'SELECT borrower_last FROM loans', 'SELECT borrower_first FROM loans',
        *[f'SELECT {c} FROM loans' for c in PEOPLE_COLUMNS],
        'SELECT name FROM broker_companies', 'SELECT nmls_id FROM broker_companies',
        'SELECT address FROM broker_companies', 'SELECT phone FROM broker_companies',
        "SELECT value FROM settings WHERE key='company_name'",
    ]
    for sql in queries:
        for (value,) in conn.execute(sql):
            if value is not None and len(str(value).strip()) >= 4:
                values.add(str(value).strip().lower())
    return values


def _text_cells(conn):
    tables = [r[0] for r in conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")]
    for table in tables:
        columns = [r[1] for r in conn.execute(f'PRAGMA table_info("{table}")')]
        for row in conn.execute(f'SELECT * FROM "{table}"'):
            for column, value in zip(columns, row):
                if isinstance(value, str):
                    yield table, column, value.lower()


def create_obfuscated_copy(source_path, target_path, key=None):
    if os.path.abspath(source_path) == os.path.abspath(target_path):
        raise ValueError('Target must be a different file; the live database is never modified.')
    if os.path.exists(target_path):
        raise FileExistsError(target_path)
    source = sqlite3.connect(f'file:{source_path}?mode=ro', uri=True)
    target = sqlite3.connect(target_path)
    try:
        source.backup(target)
        originals = _sensitive_values(source)
    finally:
        source.close()

    m = Mapper(key)
    conn = target
    conn.execute('PRAGMA foreign_keys=OFF')

    # Loans
    rows = conn.execute('SELECT id, loan_number, broker_company, investor_name, borrower_first, borrower_last, '
                        f'{", ".join(PEOPLE_COLUMNS)} FROM loans ORDER BY id').fetchall()
    for index, row in enumerate(rows, start=1):
        loan_id, _number, broker, investor, first, last, *people = row
        conn.execute(f'''UPDATE loans SET loan_number=?, broker_company=?, investor_name=?, borrower_first=?,
                         borrower_last=?, {", ".join(f"{c}=?" for c in PEOPLE_COLUMNS)} WHERE id=?''',
                     (f'OBF-{index:05d}', m.institution(broker), m.investor(investor), m.first(first), m.last(last),
                      *[m.person(p) for p in people], loan_id))

    # Broker directory (names must match loans.broker_company exactly)
    for broker_id, name, state in conn.execute('SELECT id, name, state FROM broker_companies').fetchall():
        conn.execute('''UPDATE broker_companies SET name=?, nmls_id=?, address=NULL, zip_code=NULL,
                        city=NULL, phone=NULL WHERE id=?''',
                     (m.institution(name), f'{9000000 + broker_id}', broker_id))

    conn.execute("UPDATE settings SET value='Sample Mortgage Co.' WHERE key='company_name'")

    # Tables whose free text can hold any original value. Audit rows are append-only via
    # triggers in the live DB; the copy starts a fresh, empty chain.
    for trigger in ('trg_audit_no_update', 'trg_audit_no_delete'):
        conn.execute(f'DROP TRIGGER IF EXISTS {trigger}')
    for sql in ('DELETE FROM audit_events', 'DELETE FROM login_attempts', 'DELETE FROM import_batches',
                'UPDATE loans SET import_batch_id=NULL', 'DELETE FROM users',
                "UPDATE loan_events SET details='[removed in obfuscated copy]', actor_user_id=NULL"):
        try:
            conn.execute(sql)
        except sqlite3.OperationalError:
            pass  # table absent in an older schema
    conn.execute("""CREATE TRIGGER IF NOT EXISTS trg_audit_no_update BEFORE UPDATE ON audit_events
                    BEGIN SELECT RAISE(ABORT, 'audit_events is append-only'); END""")
    conn.execute("""CREATE TRIGGER IF NOT EXISTS trg_audit_no_delete BEFORE DELETE ON audit_events
                    BEGIN SELECT RAISE(ABORT, 'audit_events is append-only'); END""")
    conn.commit()
    conn.execute('VACUUM')  # physically remove deleted pages that could still hold old text

    # A real value that coincides with an invented one (e.g. a borrower really named "Quinn")
    # is not a leak: it can't be told apart from the generated data.
    fakes = {v.lower() for table in m.maps.values() for v in table.values()}
    originals = {v for v in originals if v not in fakes and not any(v in f for f in fakes)}
    leaks = sorted({(t, c) for t, c, v in _text_cells(conn) if v in originals})
    conn.close()
    if leaks:
        os.remove(target_path)
        raise RuntimeError(f'Obfuscation check failed; original values remained in {leaks}. Copy deleted.')
    with open(target_path, 'rb') as handle:
        raw = handle.read().lower()
    if any(value.encode() in raw for value in originals if len(value) >= 6):
        os.remove(target_path)
        raise RuntimeError('Obfuscation check failed at byte level. Copy deleted.')

    return {'target': target_path, 'loans': len(rows),
            'institutions_renamed': len(m.maps.get('institution', {})),
            'people_renamed': len(m.maps.get('person', {})) + len(m.maps.get('first', {})) + len(m.maps.get('last', {})),
            'original_values_checked': len(originals), 'leaks': 0}
