#!/usr/bin/env python3
"""
Q4 (remainder) — de-gender the Spin Wheel "Sexual Preferences" pool.

The binary (yes/no + true/false) Desire Sync questions were neutralized in G3.
This script handles the remaining 100 non-binary `single_choice` questions:
50 sex='female' and 50 sex='male', used by the Spin Wheel category.

Strategy:
  * female non-binary -> keep as the canonical neutral question, rewrite any
    gendered pronouns in the question text or option text, set sex='neutral'
  * male   non-binary -> deleted (duplicate or anatomy-specific variant; the
    female receiver perspective is more universal for a gender-neutral pool)

It edits BOTH the source JSON (seed/questions/sexual_preferences.json) and the
shipped asset DB (app/src/main/assets/database/app.db) so they stay in sync.
Only row data is touched — never the schema — so Room's identity hash is safe.
build_db.py is NOT run.

One-off migration kept in the repo for traceability.
"""
import json
import os
import sqlite3

HERE = os.path.dirname(os.path.abspath(__file__))
JSON_PATH = os.path.join(HERE, "questions", "sexual_preferences.json")
DB_PATH = os.path.join(HERE, "..", "app", "src", "main", "assets", "database", "app.db")

# Question text rewrites for the 10 female questions that contain gendered pronouns.
# (The other 40 female questions are already neutral — only sex= needs to change.)
Q_REWRITES = {
    "sexual_preferences_female_015": "How should your partner initiate more often?",
    "sexual_preferences_female_024": "What is sexiest for your partner to wear?",
    "sexual_preferences_female_027": "Where would you most like your partner to start teasing you?",
    "sexual_preferences_female_030": "What do you want your partner to improve first?",
    "sexual_preferences_female_093": "How do you prefer to teach your partner about your body?",
    "sexual_preferences_female_108": "What should your partner do if you seem uncomfortable?",
    "sexual_preferences_female_150": "What question should your partner ask first?",
}

# Option text rewrites: {question_id: {old_text: new_text, ...}}
OPT_REWRITES = {
    "sexual_preferences_female_015": {
        "Say what he wants": "Say what they want",
    },
    "sexual_preferences_female_024": {
        "Boxers": "Underwear",
    },
    "sexual_preferences_female_045": {
        "He leads": "They lead",
    },
    "sexual_preferences_female_072": {
        "His shirt": "Partner's shirt",
    },
    "sexual_preferences_female_090": {
        "He watches only": "Partner watches only",
        "He tells me what to do": "Partner directs me",
    },
    "sexual_preferences_female_093": {
        "Show him": "Show them",
        "Guide his hand": "Guide their hand",
        "Tell him after": "Tell them after",
    },
}


def apply_opt_rewrites(answer_config_obj, qid):
    """Return updated answer_config dict if options need rewriting, else same obj."""
    rewrites = OPT_REWRITES.get(qid)
    if not rewrites:
        return answer_config_obj, False
    # DB format: {"type": ..., "config": {"options": [...]}}
    # JSON format may be nested the same way or flat
    inner = answer_config_obj.get("config", answer_config_obj)
    opts = inner.get("options") or []
    changed = False
    for opt in opts:
        old = opt.get("text", "")
        if old in rewrites:
            opt["text"] = rewrites[old]
            changed = True
    return answer_config_obj, changed


def migrate_json():
    with open(JSON_PATH) as f:
        data = json.load(f)
    items = data["questions"]

    kept = []
    female_updated = male_deleted = 0
    for q in items:
        if q.get("type") != "single_choice":
            kept.append(q)
            continue
        sex = q.get("sex")
        if sex == "male":
            male_deleted += 1
            continue  # delete the male variant
        if sex == "female":
            # Rewrite question text if needed
            if q["id"] in Q_REWRITES:
                q["text"] = Q_REWRITES[q["id"]]
            # Rewrite option text if needed
            cfg = q.get("answer_config") or {}
            cfg, _ = apply_opt_rewrites(cfg, q["id"])
            q["answer_config"] = cfg
            q["sex"] = "neutral"
            female_updated += 1
        kept.append(q)

    data["questions"] = kept
    with open(JSON_PATH, "w") as f:
        json.dump(data, f, indent=2, ensure_ascii=False)
        f.write("\n")
    print(f"JSON: {female_updated} female->neutral, {male_deleted} male deleted, {len(kept)} total")


def migrate_db():
    con = sqlite3.connect(DB_PATH)
    cur = con.cursor()
    rows = cur.execute(
        "SELECT id, sex, answer_config FROM question WHERE category_id='sexual_preferences'"
    ).fetchall()

    female_updated = male_deleted = 0
    for qid, sex, cfg_text in rows:
        # Only touch single_choice non-binary rows (binary pool was handled by G3)
        # We identify them by sex being 'female' or 'male' (neutral = already done)
        if sex not in ("female", "male"):
            continue

        if sex == "male":
            cur.execute("DELETE FROM question WHERE id=?", (qid,))
            male_deleted += 1
            continue

        # sex == 'female': rewrite text + options, set sex='neutral'
        new_q_text = Q_REWRITES.get(qid)

        # Parse and possibly rewrite options
        try:
            cfg = json.loads(cfg_text)
        except (json.JSONDecodeError, TypeError):
            cfg = {}
        cfg, opts_changed = apply_opt_rewrites(cfg, qid)
        new_cfg_text = json.dumps(cfg, ensure_ascii=False) if opts_changed else cfg_text

        if new_q_text and opts_changed:
            cur.execute(
                "UPDATE question SET text=?, answer_config=?, sex='neutral' WHERE id=?",
                (new_q_text, new_cfg_text, qid),
            )
        elif new_q_text:
            cur.execute(
                "UPDATE question SET text=?, sex='neutral' WHERE id=?",
                (new_q_text, qid),
            )
        elif opts_changed:
            cur.execute(
                "UPDATE question SET answer_config=?, sex='neutral' WHERE id=?",
                (new_cfg_text, qid),
            )
        else:
            cur.execute("UPDATE question SET sex='neutral' WHERE id=?", (qid,))
        female_updated += 1

    con.commit()
    con.close()
    print(f"DB:   {female_updated} female->neutral, {male_deleted} male deleted")


if __name__ == "__main__":
    migrate_json()
    migrate_db()
