"""
One-off script: import dataset_2026.csv into platform.db.

Usage:
  .venv\Scripts\python.exe import_dataset.py
  .venv\Scripts\python.exe import_dataset.py --org-name "Ma Banque" --org-slug "ma-banque"

If the organisation already exists (by slug) its existing entries are kept
and only NEW rows are appended.  Re-run safely at any time.
"""
import argparse, csv, os, sqlite3, hashlib

BASE_DIR = os.path.abspath(os.path.dirname(__file__))
DB_PATH  = os.path.join(BASE_DIR, "platform.db")
CSV_PATH = os.path.join(BASE_DIR, "dataset_2026.csv")

# ── CLI args ──────────────────────────────────────────────────────────────────
parser = argparse.ArgumentParser()
parser.add_argument("--org-name", default="Banque DZ",  help="Display name of the organisation")
parser.add_argument("--org-slug", default="banque-dz",  help="URL-safe unique slug")
parser.add_argument("--username", default="admin",      help="Admin username")
parser.add_argument("--password", default="admin123",   help="Admin password (min 6 chars)")
parser.add_argument("--clear",    action="store_true",  help="Delete existing entries for this org before import")
args = parser.parse_args()

# ── DB helpers ────────────────────────────────────────────────────────────────
def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def init_db(conn):
    conn.executescript("""
    CREATE TABLE IF NOT EXISTS organisations (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        name       TEXT    NOT NULL UNIQUE,
        slug       TEXT    NOT NULL UNIQUE,
        created_at TEXT    DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS users (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        org_id     INTEGER NOT NULL REFERENCES organisations(id),
        username   TEXT    NOT NULL UNIQUE,
        password   TEXT    NOT NULL,
        role       TEXT    NOT NULL DEFAULT 'admin',
        created_at TEXT    DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS entries (
        id           INTEGER PRIMARY KEY AUTOINCREMENT,
        org_id       INTEGER NOT NULL REFERENCES organisations(id),
        processus    TEXT,
        procedure    TEXT,
        intent       TEXT,
        sub_intent   TEXT,
        question     TEXT    NOT NULL,
        response     TEXT    NOT NULL,
        service      TEXT,
        service_link TEXT,
        latitude     REAL,
        longitude    REAL,
        created_at   TEXT    DEFAULT CURRENT_TIMESTAMP
    );
    """)

def hash_password(pw: str) -> str:
    return hashlib.sha256(pw.encode()).hexdigest()

def to_float(val: str):
    if not val or not val.strip():
        return None
    try:
        return float(val.strip().replace(",", "."))
    except ValueError:
        return None

# ── Main ──────────────────────────────────────────────────────────────────────
with get_db() as conn:
    init_db(conn)

    # 1. Get or create organisation
    row = conn.execute("SELECT id FROM organisations WHERE slug=?", (args.org_slug,)).fetchone()
    if row:
        org_id = row["id"]
        print(f"✓ Organisation existante trouvée  (id={org_id}, slug='{args.org_slug}')")
    else:
        cur = conn.execute(
            "INSERT INTO organisations (name, slug) VALUES (?,?)",
            (args.org_name, args.org_slug)
        )
        org_id = cur.lastrowid
        print(f"✓ Organisation créée  (id={org_id}, name='{args.org_name}')")

    # 2. Ensure admin user exists for this org
    u = conn.execute("SELECT id, username FROM users WHERE org_id=?", (org_id,)).fetchone()
    if not u:
        # Make username unique by appending slug if 'admin' is taken
        username = args.username
        if conn.execute("SELECT id FROM users WHERE username=?", (username,)).fetchone():
            username = f"{args.username}-{args.org_slug}"
        conn.execute(
            "INSERT INTO users (org_id, username, password, role) VALUES (?,?,?,?)",
            (org_id, username, hash_password(args.password), "admin")
        )
        print(f"✓ Utilisateur admin créé  (username='{username}', password='{args.password}')")
    else:
        username = u["username"]
        print(f"✓ Utilisateur admin déjà existant  (username='{username}')")

    # 3. Optionally clear existing entries
    if args.clear:
        deleted = conn.execute("DELETE FROM entries WHERE org_id=?", (org_id,)).rowcount
        print(f"🗑  {deleted} entrées existantes supprimées (--clear)")

    # 4. Read CSV and insert
    if not os.path.exists(CSV_PATH):
        print(f"✗ Fichier introuvable : {CSV_PATH}")
        raise SystemExit(1)

    inserted = skipped = errors = 0
    with open(CSV_PATH, encoding="utf-8-sig", newline="") as f:
        reader = csv.DictReader(f, delimiter=";")
        for i, row in enumerate(reader, start=2):  # line 2 = first data row
            q = (row.get("Question") or "").strip()
            r = (row.get("Response") or "").strip()
            if not q or not r:
                skipped += 1
                continue
            try:
                conn.execute(
                    """INSERT INTO entries
                       (org_id, processus, procedure, intent, sub_intent,
                        question, response, service, service_link, latitude, longitude)
                       VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
                    (
                        org_id,
                        (row.get("Processus") or "").strip() or None,
                        (row.get("Procedure") or "").strip() or None,
                        (row.get("Intent") or "").strip() or None,
                        (row.get("SubIntent") or "").strip() or None,
                        q, r,
                        (row.get("Service") or "").strip() or None,
                        (row.get("ServiceLink") or "").strip() or None,
                        to_float(row.get("Latitude", "")),
                        to_float(row.get("Longitude", "")),
                    )
                )
                inserted += 1
            except Exception as e:
                print(f"  ⚠  Ligne {i} ignorée : {e}")
                errors += 1

    print(f"\n✅ Import terminé : {inserted} insérées | {skipped} ignorées (vides) | {errors} erreurs")
    print(f"   Organisation : '{args.org_name}'  (id={org_id})")
    print(f"   Login admin  : username='{username}'  password='{args.password}'")