#!/usr/bin/env python3
"""Owner Listing — dump the source SQLite archive to TSV for MySQL LOAD DATA.

This is the ONLY part of the import that is not PHP, and only because PHP on
this host has no sqlite driver. It moves bytes and nothing else: every decision
about the data (name tokenising, property merging, the owner profile) is made in
PHP by `owner:build-index`, so there is exactly one implementation of each and
the search cannot disagree with the index it reads.

    python3 scripts/owner-listing/export-sqlite.py \
        --db owner_search.db --out /tmp/owner-export

Writes owner_records.NNN.tsv, ready for:

    php artisan owner:import /tmp/owner-export

Escaping matches MySQL's LOAD DATA defaults (FIELDS ESCAPED BY '\\'): a
backslash, tab, newline or carriage return inside a value is escaped, and a
NULL is the two characters \\N. Names and addresses in this archive genuinely
do contain tabs and newlines, so writing raw values would silently shift
columns mid-file.
"""
import argparse
import os
import sqlite3
import sys

# Source column -> destination column. The destination order is the order of
# the columns in the owner_records migration, and `owner:import` names them in
# the same order, so the two must be changed together.
COLUMNS = [
    ('rec_id', 'id'),
    ('phone', 'phone'),
    ('phone_slot', 'phone_slot'),
    ('name', 'name'),
    ('email', 'email'),
    ('country', 'country'),
    ('unit', 'unit'),
    ('address', 'address'),
    ('project', 'project'),
    ('pkey', 'property_key'),
    ('area', 'area'),
    ('state', 'state'),
    ('status', 'status'),
    ('category', 'category'),
    # NULL in the archive means "not flagged", and the column is NOT NULL here.
    ('COALESCE(absentee, 0)', 'is_absentee'),
    ('source', 'source'),
    ('vintage', 'vintage'),
]

_ESCAPES = (('\\', '\\\\'), ('\t', '\\t'), ('\n', '\\n'), ('\r', '\\r'))


def cell(value):
    """One TSV field, escaped for MySQL LOAD DATA."""
    if value is None:
        return '\\N'
    if isinstance(value, (int, float)):
        return str(value)
    text = str(value)
    for raw, escaped in _ESCAPES:
        text = text.replace(raw, escaped)
    return text


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument('--db', required=True, help='path to owner_search.db')
    ap.add_argument('--out', required=True, help='directory to write TSV chunks into')
    ap.add_argument('--chunk', type=int, default=500_000, help='rows per file')
    args = ap.parse_args()

    if not os.path.exists(args.db):
        sys.exit('archive not found: ' + args.db)
    os.makedirs(args.out, exist_ok=True)

    conn = sqlite3.connect(args.db)
    conn.text_factory = str
    total = conn.execute('SELECT COUNT(*) FROM records').fetchone()[0]
    print(f'{total:,} records in {args.db}', flush=True)

    select = ', '.join(src for src, _ in COLUMNS)
    cursor = conn.execute(f'SELECT {select} FROM records ORDER BY rec_id')

    written = index = 0
    handle = None
    manifest = []
    try:
        for row in cursor:
            if written % args.chunk == 0:
                if handle:
                    handle.close()
                name = f'owner_records.{index:03d}.tsv'
                path = os.path.join(args.out, name)
                handle = open(path, 'w', encoding='utf-8', newline='')
                manifest.append(name)
                index += 1
            handle.write('\t'.join(cell(v) for v in row))
            handle.write('\n')
            written += 1
            if written % 250_000 == 0:
                print(f'  {written:,} / {total:,}', flush=True)
    finally:
        if handle:
            handle.close()
        conn.close()

    # The importer reads this rather than globbing, so a half-finished export
    # cannot be loaded as if it were complete.
    with open(os.path.join(args.out, 'manifest.txt'), 'w', encoding='utf-8') as fh:
        fh.write(f'rows\t{written}\n')
        fh.write('columns\t' + ','.join(dest for _, dest in COLUMNS) + '\n')
        for name in manifest:
            fh.write(f'chunk\t{name}\n')

    print(f'{written:,} rows -> {args.out} ({len(manifest)} files)')
    if written != total:
        sys.exit(f'INCOMPLETE: wrote {written} of {total}')


if __name__ == '__main__':
    main()