sqlite_handler.py

import re
import sqlite3
from contextlib import contextmanager
from datetime import datetime
from typing import Optional

import config_handler

INQUIRY_STATUSES = ["対応中", "検討中", "保留"]
PROJECT_STATUSES = ["見積作成中", "決済待", "制作中", "検品中", "入金待"]


@contextmanager
def _conn():
    db_path = config_handler.get_data_path()
    con = sqlite3.connect(str(db_path))
    con.row_factory = sqlite3.Row
    _ensure_schema(con)
    try:
        yield con
        con.commit()
    finally:
        con.close()


def _ensure_schema(con):
    _ensure_client_schema(con)
    exists = con.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='engagements'"
    ).fetchone()
    if not exists:
        return
    cols = {row[1] for row in con.execute("PRAGMA table_info(engagements)").fetchall()}
    if "kakunin_date" not in cols:
        _add_column_if_missing(con, "engagements", "kakunin_date", "TEXT DEFAULT ''")
    if "revision_count" not in cols:
        _add_column_if_missing(con, "engagements", "revision_count", "INTEGER DEFAULT 0")
    if "client_id" not in cols:
        _add_column_if_missing(con, "engagements", "client_id", "INTEGER")
    if "contact_id" not in cols:
        _add_column_if_missing(con, "engagements", "contact_id", "INTEGER")
    client_cols = {row[1] for row in con.execute("PRAGMA table_info(clients)").fetchall()}
    if "payment_terms" not in client_cols:
        _add_column_if_missing(con, "clients", "payment_terms", "TEXT DEFAULT ''")
    _backfill_clients(con)


def _add_column_if_missing(con, table: str, column: str, definition: str):
    try:
        con.execute(f"ALTER TABLE {table} ADD COLUMN {column} {definition}")
    except sqlite3.OperationalError as e:
        if "duplicate column name" not in str(e).lower():
            raise


def _now() -> str:
    return datetime.now().strftime('%Y/%m/%d %H:%M')


def _ensure_client_schema(con):
    con.execute("""
    CREATE TABLE IF NOT EXISTS clients (
        id           INTEGER PRIMARY KEY AUTOINCREMENT,
        client_name  TEXT NOT NULL UNIQUE,
        aliases      TEXT DEFAULT '',
        risk_level   TEXT DEFAULT '通常',
        payment_terms TEXT DEFAULT '',
        summary_note TEXT DEFAULT '',
        created_at   TEXT DEFAULT '',
        updated_at   TEXT DEFAULT ''
    )""")
    con.execute("""
    CREATE TABLE IF NOT EXISTS client_contacts (
        id           INTEGER PRIMARY KEY AUTOINCREMENT,
        client_id    INTEGER NOT NULL,
        contact_name TEXT DEFAULT '',
        email        TEXT DEFAULT '',
        phone        TEXT DEFAULT '',
        role         TEXT DEFAULT '',
        note         TEXT DEFAULT '',
        risk_note    TEXT DEFAULT '',
        is_primary   INTEGER DEFAULT 0,
        created_at   TEXT DEFAULT '',
        updated_at   TEXT DEFAULT '',
        FOREIGN KEY(client_id) REFERENCES clients(id)
    )""")
    con.execute("""
    CREATE TABLE IF NOT EXISTS client_notes (
        id                  INTEGER PRIMARY KEY AUTOINCREMENT,
        client_id           INTEGER NOT NULL,
        contact_id          INTEGER,
        related_uketsuke_no TEXT DEFAULT '',
        related_anken_no    TEXT DEFAULT '',
        note_type           TEXT DEFAULT 'メモ',
        severity            TEXT DEFAULT '低',
        note                TEXT DEFAULT '',
        recorded_at         TEXT DEFAULT '',
        is_active           INTEGER DEFAULT 1,
        FOREIGN KEY(client_id) REFERENCES clients(id),
        FOREIGN KEY(contact_id) REFERENCES client_contacts(id)
    )""")
    con.execute("""
    CREATE TABLE IF NOT EXISTS client_agreements (
        id                INTEGER PRIMARY KEY AUTOINCREMENT,
        client_id         INTEGER NOT NULL,
        agreement_type    TEXT DEFAULT 'その他',
        title             TEXT DEFAULT '',
        status            TEXT DEFAULT '有効',
        effective_date    TEXT DEFAULT '',
        expiry_date       TEXT DEFAULT '',
        summary           TEXT DEFAULT '',
        details           TEXT DEFAULT '',
        file_path         TEXT DEFAULT '',
        related_anken_no  TEXT DEFAULT '',
        created_at        TEXT DEFAULT '',
        updated_at        TEXT DEFAULT '',
        FOREIGN KEY(client_id) REFERENCES clients(id)
    )""")


def _get_or_create_client_con(con, client_name: str) -> Optional[int]:
    name = _as_str(client_name)
    if not name:
        return None
    row = con.execute("SELECT id FROM clients WHERE client_name = ?", (name,)).fetchone()
    if row:
        return int(row["id"])
    now = _now()
    cur = con.execute(
        "INSERT INTO clients (client_name, created_at, updated_at) VALUES (?, ?, ?)",
        (name, now, now),
    )
    return int(cur.lastrowid)


def _get_or_create_contact_con(con, client_id: Optional[int], contact_name: str = '',
                               email: str = '', phone: str = '') -> Optional[int]:
    if not client_id:
        return None
    name = _as_str(contact_name)
    mail = _as_str(email)
    tel = _as_str(phone)
    if not name and not mail and not tel:
        return None
    row = con.execute(
        """
        SELECT id FROM client_contacts
        WHERE client_id = ?
          AND COALESCE(contact_name, '') = ?
          AND COALESCE(email, '') = ?
        """,
        (client_id, name, mail),
    ).fetchone()
    if row:
        return int(row["id"])
    exists = con.execute(
        "SELECT id FROM client_contacts WHERE client_id = ? LIMIT 1", (client_id,)
    ).fetchone()
    now = _now()
    cur = con.execute(
        """
        INSERT INTO client_contacts
            (client_id, contact_name, email, phone, is_primary, created_at, updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        """,
        (client_id, name, mail, tel, 0 if exists else 1, now, now),
    )
    return int(cur.lastrowid)


def _link_engagement_client_con(con, row_id: int, kaisha_mei: str, irai_sha: str, renraku_saki: str):
    client_id = _get_or_create_client_con(con, kaisha_mei)
    contact_id = _get_or_create_contact_con(con, client_id, irai_sha, renraku_saki)
    con.execute(
        "UPDATE engagements SET client_id = ?, contact_id = ? WHERE id = ?",
        (client_id, contact_id, row_id),
    )
    return client_id, contact_id


def _backfill_clients(con):
    exists = con.execute(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='engagements'"
    ).fetchone()
    if not exists:
        return
    cols = {row[1] for row in con.execute("PRAGMA table_info(engagements)").fetchall()}
    if "client_id" not in cols or "contact_id" not in cols:
        return
    rows = con.execute(
        """
        SELECT id, kaisha_mei, irai_sha, renraku_saki
        FROM engagements
        WHERE (client_id IS NULL OR client_id = 0)
          AND COALESCE(kaisha_mei, '') != ''
        """
    ).fetchall()
    for row in rows:
        _link_engagement_client_con(
            con,
            row["id"],
            row["kaisha_mei"],
            row["irai_sha"],
            row["renraku_saki"],
        )


def init_db():
    with _conn() as con:
        con.execute("""
        CREATE TABLE IF NOT EXISTS engagements (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            uketsuke_no   TEXT UNIQUE NOT NULL,
            uketsuke_date TEXT DEFAULT '',
            irai_sha      TEXT DEFAULT '',
            kaisha_mei    TEXT DEFAULT '',
            renraku_saki  TEXT DEFAULT '',
            toiawase      TEXT DEFAULT '',
            co            TEXT DEFAULT 'W',
            renraku_date  TEXT DEFAULT '',
            phase         TEXT DEFAULT 'inquiry',
            status        TEXT DEFAULT '対応中',
            end_state     TEXT DEFAULT '',
            anken_no      TEXT DEFAULT '',
            anken_mei     TEXT DEFAULT '',
            irai_date     TEXT DEFAULT '',
            kingaku       TEXT DEFAULT '',
            juchu_date    TEXT DEFAULT '',
            kakunin_date  TEXT DEFAULT '',
            nouki         TEXT DEFAULT '',
            sagyou_kanryo TEXT DEFAULT '',
            nyukin        TEXT DEFAULT '',
            botsu_riyu    TEXT DEFAULT '',
            revision_count INTEGER DEFAULT 0,
            memo          TEXT DEFAULT '',
            created_at    TEXT DEFAULT ''
        )""")


def _as_str(v) -> str:
    s = str(v).strip() if v is not None else ''
    return '' if s in ('nan', 'NaN', 'None') else s


def _row_to_inquiry_dict(row) -> dict:
    r = dict(row)
    status = 'ボツ' if _as_str(r.get('end_state')) == 'ボツ' else (_as_str(r['status']) or '対応中')
    return {
        '受付No':          _as_str(r['uketsuke_no']),
        '受付日':          _as_str(r['uketsuke_date']),
        '依頼者':          _as_str(r['irai_sha']),
        '会社名':          _as_str(r['kaisha_mei']),
        '連絡先':          _as_str(r['renraku_saki']),
        '件名':            _as_str(r['anken_mei']),
        '問い合わせ内容':  _as_str(r['toiawase']),
        'Co':              _as_str(r['co']) or 'W',
        'ステータス':      status,
        '次回連絡日':      _as_str(r['renraku_date']),
        '案件番号':        _as_str(r['anken_no']),
        '進捗メモ':        _as_str(r['memo']),
        'phase':           _as_str(r['phase']),
        'end_state':       _as_str(r['end_state']),
        'client_id':        r.get('client_id') or '',
        'contact_id':       r.get('contact_id') or '',
    }


def _row_to_project_dict(row) -> dict:
    r = dict(row)
    end = _as_str(r.get('end_state'))
    status = end if end in ('完了', 'ボツ') else (_as_str(r['status']) or '見積作成中')
    return {
        '管理番号':   _as_str(r['anken_no']),
        '受付No':     _as_str(r['uketsuke_no']),
        '受付日':     _as_str(r['uketsuke_date']),
        '依頼日':     _as_str(r['irai_date']),
        '依頼者':     _as_str(r['irai_sha']),
        '会社名':     _as_str(r['kaisha_mei']),
        '案件名':     _as_str(r['anken_mei']),
        '金額':       _as_str(r['kingaku']),
        '連絡先':     _as_str(r['renraku_saki']),
        '受注日':     _as_str(r['juchu_date']),
        '確認日':     _as_str(r.get('kakunin_date')),
        '納期':       _as_str(r['nouki']),
        '作業完了日': _as_str(r['sagyou_kanryo']),
        '入金':       _as_str(r['nyukin']),
        'Co':         _as_str(r['co']) or 'W',
        'ステータス': status,
        'end_state':  end,
        '次回連絡日': _as_str(r['renraku_date']),
        '進捗メモ':   _as_str(r['memo']),
        'ボツの理由': _as_str(r['botsu_riyu']),
        '修正回数':   _as_str(r.get('revision_count')) or '0',
        'phase':      _as_str(r['phase']),
        'client_id':   r.get('client_id') or '',
        'contact_id':  r.get('contact_id') or '',
    }


def get_inquiry_rows() -> list:
    with _conn() as con:
        rows = con.execute(
            "SELECT * FROM engagements WHERE phase='inquiry' AND end_state='' ORDER BY uketsuke_date DESC"
        ).fetchall()
    return [_row_to_inquiry_dict(r) for r in rows]


def get_project_rows() -> list:
    with _conn() as con:
        rows = con.execute(
            "SELECT * FROM engagements WHERE phase='project' AND end_state='' "
            "ORDER BY CAST(SUBSTR(anken_no, 2) AS INTEGER) DESC, anken_no DESC"
        ).fetchall()
    return [_row_to_project_dict(r) for r in rows]


def get_kanryo_rows() -> list:
    with _conn() as con:
        rows = con.execute(
            "SELECT * FROM engagements WHERE phase='project' AND end_state='完了' "
            "ORDER BY sagyou_kanryo DESC"
        ).fetchall()
    return [_row_to_project_dict(r) for r in rows]


def get_botsu_rows() -> list:
    with _conn() as con:
        rows = con.execute(
            "SELECT * FROM engagements WHERE end_state='ボツ' ORDER BY uketsuke_date DESC"
        ).fetchall()
    result = []
    for r in rows:
        d = dict(r)
        if d['phase'] == 'project':
            result.append(_row_to_project_dict(r))
        else:
            inq = _row_to_inquiry_dict(r)
            inq['管理番号'] = ''
            inq['依頼日'] = inq.get('受付日', '')
            inq['案件名'] = inq.get('件名') or inq.get('問い合わせ内容', '')
            inq['金額'] = ''
            inq['ボツの理由'] = inq.get('進捗メモ', '')
            result.append(inq)
    return result


def get_all_rows() -> list:
    with _conn() as con:
        rows = con.execute("SELECT * FROM engagements ORDER BY uketsuke_date DESC").fetchall()
    result = []
    for r in rows:
        d = dict(r)
        if d['phase'] == 'project':
            result.append(_row_to_project_dict(r))
        else:
            result.append(_row_to_inquiry_dict(r))
    return result


def get_by_uketsuke_no(uketsuke_no: str) -> Optional[dict]:
    with _conn() as con:
        row = con.execute(
            "SELECT * FROM engagements WHERE uketsuke_no=?", (uketsuke_no,)
        ).fetchone()
    if not row:
        return None
    r = dict(row)
    return _row_to_project_dict(row) if r['phase'] == 'project' else _row_to_inquiry_dict(row)


def get_by_anken_no(anken_no: str) -> Optional[dict]:
    with _conn() as con:
        row = con.execute(
            "SELECT * FROM engagements WHERE anken_no=?", (anken_no,)
        ).fetchone()
    if not row:
        return None
    return _row_to_project_dict(row)


def create_inquiry(uketsuke_no: str, uketsuke_date: str, irai_sha: str, kaisha_mei: str,
                   renraku_saki: str = '', toiawase: str = '', co: str = 'W',
                   memo: str = '', title: str = '') -> str:
    with _conn() as con:
        client_id = _get_or_create_client_con(con, kaisha_mei)
        contact_id = _get_or_create_contact_con(con, client_id, irai_sha, renraku_saki)
        con.execute("""
        INSERT INTO engagements (uketsuke_no, uketsuke_date, irai_sha, kaisha_mei,
            renraku_saki, anken_mei, toiawase, co, memo, phase, status, client_id, contact_id, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'inquiry', '対応中', ?, ?, ?)
        """, (uketsuke_no, uketsuke_date, irai_sha, kaisha_mei,
              renraku_saki, title, toiawase, co, memo,
              client_id, contact_id, _now()))
    return uketsuke_no


def update_inquiry(uketsuke_no: str, **fields):
    allowed = {'uketsuke_date', 'irai_sha', 'kaisha_mei', 'renraku_saki', 'toiawase',
               'co', 'renraku_date', 'status', 'end_state', 'memo', 'anken_mei',
               'client_id', 'contact_id'}
    sets = {k: v for k, v in fields.items() if k in allowed}
    with _conn() as con:
        row = con.execute("SELECT * FROM engagements WHERE uketsuke_no=?", (uketsuke_no,)).fetchone()
        if row:
            kaisha = sets.get("kaisha_mei", row["kaisha_mei"])
            irai = sets.get("irai_sha", row["irai_sha"])
            renraku = sets.get("renraku_saki", row["renraku_saki"])
            client_id = _get_or_create_client_con(con, kaisha)
            contact_id = _get_or_create_contact_con(con, client_id, irai, renraku)
            sets["client_id"] = client_id
            sets["contact_id"] = contact_id
        if not sets:
            return
        sql = "UPDATE engagements SET " + ", ".join(f"{k}=?" for k in sets) + " WHERE uketsuke_no=?"
        con.execute(sql, list(sets.values()) + [uketsuke_no])


def promote_to_project(uketsuke_no: str, anken_no: str, anken_mei: str,
                        kingaku: str = '', nouki: str = '', memo: str = ''):
    irai_date = datetime.now().strftime('%Y/%m/%d')
    with _conn() as con:
        con.execute("""
        UPDATE engagements SET
            phase='project', status='見積作成中', end_state='',
            anken_no=?, anken_mei=?, kingaku=?, nouki=?, memo=?, irai_date=?
        WHERE uketsuke_no=?
        """, (anken_no, anken_mei, kingaku, nouki, memo, irai_date, uketsuke_no))


def update_project(current_anken_no: str, **fields):
    allowed = {'status', 'end_state', 'kingaku', 'juchu_date', 'nouki', 'sagyou_kanryo',
               'nyukin', 'botsu_riyu', 'memo', 'anken_no', 'kaisha_mei', 'irai_sha',
               'co', 'anken_mei', 'irai_date', 'kakunin_date', 'revision_count',
               'client_id', 'contact_id', 'renraku_saki', 'renraku_date'}
    sets = {k: v for k, v in fields.items() if k in allowed}
    with _conn() as con:
        row = con.execute("SELECT * FROM engagements WHERE anken_no=?", (current_anken_no,)).fetchone()
        if row:
            if any(k in sets for k in ("kaisha_mei", "irai_sha", "renraku_saki")):
                kaisha = sets.get("kaisha_mei", row["kaisha_mei"])
                irai = sets.get("irai_sha", row["irai_sha"])
                renraku = sets.get("renraku_saki", row["renraku_saki"])
                client_id = _get_or_create_client_con(con, kaisha)
                contact_id = _get_or_create_contact_con(con, client_id, irai, renraku)
                sets["client_id"] = client_id
                sets["contact_id"] = contact_id
        if not sets:
            return
        sql = "UPDATE engagements SET " + ", ".join(f"{k}=?" for k in sets) + " WHERE anken_no=?"
        con.execute(sql, list(sets.values()) + [current_anken_no])


def delete_row(no: str, row_type: str):
    with _conn() as con:
        if row_type == '受付':
            con.execute("DELETE FROM engagements WHERE uketsuke_no = ?", (no,))
        else:
            con.execute("DELETE FROM engagements WHERE anken_no = ?", (no,))


def list_clients(q: str = "") -> list:
    where = ""
    params = []
    q = _as_str(q)
    if q:
        where = "WHERE client_name LIKE ? OR aliases LIKE ? OR summary_note LIKE ? OR payment_terms LIKE ?"
        like = f"%{q}%"
        params = [like, like, like, like]
    with _conn() as con:
        rows = con.execute(
            f"""
            SELECT c.*,
                   (SELECT note FROM client_notes WHERE client_id = c.id AND is_active = 1 ORDER BY recorded_at DESC, id DESC LIMIT 1) AS latest_note,
                   (SELECT COUNT(*) FROM engagements WHERE client_id = c.id) AS engagement_count,
                   (SELECT COUNT(*) FROM engagements WHERE client_id = c.id AND phase = 'project') AS project_count,
                   (SELECT COUNT(*) FROM engagements WHERE client_id = c.id
                    AND (end_state = '完了' OR (end_state = '' AND phase = 'project' AND juchu_date != '' AND juchu_date IS NOT NULL))) AS juchu_count,
                   (SELECT COUNT(*) FROM engagements WHERE client_id = c.id AND end_state = 'ボツ') AS botsu_count,
                   (SELECT COALESCE(SUM(CAST(kingaku AS REAL)), 0) FROM engagements
                    WHERE client_id = c.id AND phase = 'project' AND end_state = '完了' AND kingaku != '') AS juchu_kingaku_sum,
                   (SELECT COUNT(*) FROM client_notes WHERE client_id = c.id AND is_active = 1) AS active_note_count,
                   (SELECT COUNT(*) FROM client_agreements WHERE client_id = c.id AND status = '有効' AND agreement_type = 'NDA') AS nda_count,
                   (SELECT COUNT(*) FROM client_agreements WHERE client_id = c.id AND status = '有効' AND agreement_type != 'NDA') AS yakusoku_count,
                   (SELECT MAX(uketsuke_date) FROM engagements WHERE client_id = c.id) AS latest_uketsuke_date
            FROM clients c
            {where}
            ORDER BY
              latest_uketsuke_date = '',
              latest_uketsuke_date DESC,
              c.client_name
            """,
            params,
        ).fetchall()
    result = []
    for r in rows:
        d = dict(r)
        ec = d.get('engagement_count') or 0
        pc = d.get('project_count') or 0
        jc = d.get('juchu_count') or 0
        bc = d.get('botsu_count') or 0
        denom = jc + bc
        d['juchu_rate'] = round(jc / denom * 100) if denom > 0 else 0
        result.append(d)
    return result


def get_client(client_id: int) -> Optional[dict]:
    with _conn() as con:
        row = con.execute("SELECT * FROM clients WHERE id=?", (client_id,)).fetchone()
    return dict(row) if row else None


def delete_client_if_unused(client_id: int) -> bool:
    with _conn() as con:
        row = con.execute(
            "SELECT COUNT(*) AS cnt FROM engagements WHERE client_id=?",
            (client_id,),
        ).fetchone()
        if row and int(row["cnt"]) > 0:
            return False
        con.execute("DELETE FROM client_notes WHERE client_id=?", (client_id,))
        con.execute("DELETE FROM client_agreements WHERE client_id=?", (client_id,))
        con.execute("DELETE FROM client_contacts WHERE client_id=?", (client_id,))
        cur = con.execute("DELETE FROM clients WHERE id=?", (client_id,))
        return cur.rowcount > 0


def get_client_detail(client_id: int) -> dict:
    with _conn() as con:
        client = con.execute("SELECT * FROM clients WHERE id=?", (client_id,)).fetchone()
        if not client:
            return {}
        contacts = con.execute(
            "SELECT * FROM client_contacts WHERE client_id=? ORDER BY is_primary DESC, contact_name",
            (client_id,),
        ).fetchall()
        notes = con.execute(
            """
            SELECT n.*, cc.contact_name
            FROM client_notes n
            LEFT JOIN client_contacts cc ON cc.id = n.contact_id
            WHERE n.client_id=?
            ORDER BY n.is_active DESC, n.recorded_at DESC, n.id DESC
            """,
            (client_id,),
        ).fetchall()
        agreements = con.execute(
            """
            SELECT * FROM client_agreements
            WHERE client_id=?
            ORDER BY
              CASE status WHEN '有効' THEN 0 WHEN '確認中' THEN 1 WHEN '未締結' THEN 2 ELSE 3 END,
              expiry_date = '',
              expiry_date
            """,
            (client_id,),
        ).fetchall()
        engagements = con.execute(
            "SELECT * FROM engagements WHERE client_id=? ORDER BY uketsuke_date DESC, irai_date DESC",
            (client_id,),
        ).fetchall()

    inquiries = []
    projects = []
    kanryo_count = 0
    botsu_count = 0
    kanryo_kingaku = 0
    for row in engagements:
        d = dict(row)
        end = _as_str(d.get("end_state"))
        is_juchu = d.get("phase") == "project" and bool(_as_str(d.get("juchu_date")))
        if end == "完了" or (end == "" and is_juchu):
            kanryo_count += 1
            try:
                kanryo_kingaku += int(d.get("kingaku") or 0)
            except (ValueError, TypeError):
                pass
        elif end == "ボツ":
            botsu_count += 1
        if d.get("phase") == "project":
            projects.append(_row_to_project_dict(row))
        else:
            inquiries.append(_row_to_inquiry_dict(row))
    denom = kanryo_count + botsu_count
    haschu_rate = round(kanryo_count / denom * 100) if denom > 0 else None
    return {
        "client": dict(client),
        "contacts": [dict(r) for r in contacts],
        "notes": [dict(r) for r in notes],
        "agreements": [dict(r) for r in agreements],
        "inquiries": inquiries,
        "projects": projects,
        "project_stats": {
            "kanryo_count":   kanryo_count,
            "kanryo_kingaku": kanryo_kingaku,
            "haschu_rate":    haschu_rate,
        },
    }


def update_client(client_id: int, client_name: str, aliases: str = '',
                  risk_level: str = '通常', payment_terms: str = '',
                  summary_note: str = ''):
    with _conn() as con:
        con.execute(
            """
            UPDATE clients
            SET client_name=?, aliases=?, risk_level=?, payment_terms=?, summary_note=?, updated_at=?
            WHERE id=?
            """,
            (_as_str(client_name), _as_str(aliases), _as_str(risk_level) or '通常',
             _as_str(payment_terms), _as_str(summary_note), _now(), client_id),
        )


def add_client_contact(client_id: int, contact_name: str = '', email: str = '', phone: str = '',
                       role: str = '', note: str = '', risk_note: str = '', is_primary: bool = False):
    with _conn() as con:
        if is_primary:
            con.execute("UPDATE client_contacts SET is_primary=0 WHERE client_id=?", (client_id,))
        now = _now()
        con.execute(
            """
            INSERT INTO client_contacts
              (client_id, contact_name, email, phone, role, note, risk_note, is_primary, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (client_id, _as_str(contact_name), _as_str(email), _as_str(phone),
             _as_str(role), _as_str(note), _as_str(risk_note), 1 if is_primary else 0, now, now),
        )


def add_client_note(client_id: int, contact_id: Optional[int] = None, note_type: str = 'メモ',
                    severity: str = '低', note: str = '', related_uketsuke_no: str = '',
                    related_anken_no: str = '', is_active: bool = True):
    with _conn() as con:
        con.execute(
            """
            INSERT INTO client_notes
              (client_id, contact_id, related_uketsuke_no, related_anken_no,
               note_type, severity, note, recorded_at, is_active)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (client_id, contact_id or None, _as_str(related_uketsuke_no), _as_str(related_anken_no),
             _as_str(note_type) or 'メモ', _as_str(severity) or '低', _as_str(note), _now(),
             1 if is_active else 0),
        )


def update_client_note(client_id: int, note_id: int, contact_id: Optional[int] = None,
                       note_type: str = 'メモ', severity: str = '低', note: str = '',
                       related_uketsuke_no: str = '', related_anken_no: str = '',
                       is_active: bool = True):
    with _conn() as con:
        con.execute(
            """
            UPDATE client_notes
            SET contact_id=?, related_uketsuke_no=?, related_anken_no=?,
                note_type=?, severity=?, note=?, is_active=?
            WHERE id=? AND client_id=?
            """,
            (contact_id or None, _as_str(related_uketsuke_no), _as_str(related_anken_no),
             _as_str(note_type) or 'メモ', _as_str(severity) or '低', _as_str(note),
             1 if is_active else 0, note_id, client_id),
        )


def set_client_note_active(client_id: int, note_id: int, is_active: bool):
    with _conn() as con:
        con.execute(
            "UPDATE client_notes SET is_active=? WHERE id=? AND client_id=?",
            (1 if is_active else 0, note_id, client_id),
        )


def add_client_agreement(client_id: int, agreement_type: str = 'その他', title: str = '',
                         status: str = '有効', effective_date: str = '', expiry_date: str = '',
                         summary: str = '', details: str = '', file_path: str = '',
                         related_anken_no: str = ''):
    with _conn() as con:
        now = _now()
        con.execute(
            """
            INSERT INTO client_agreements
              (client_id, agreement_type, title, status, effective_date, expiry_date,
               summary, details, file_path, related_anken_no, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (client_id, _as_str(agreement_type) or 'その他', _as_str(title), _as_str(status) or '有効',
             _as_str(effective_date), _as_str(expiry_date), _as_str(summary), _as_str(details),
             _as_str(file_path), _as_str(related_anken_no), now, now),
        )


def get_client_alert(client_id: Optional[int]) -> dict:
    if not client_id:
        return {}
    with _conn() as con:
        client = con.execute("SELECT * FROM clients WHERE id=?", (client_id,)).fetchone()
        if not client:
            return {}
        notes = con.execute(
            """
            SELECT note_type, severity, note, recorded_at
            FROM client_notes
            WHERE client_id=? AND is_active=1
            ORDER BY
              CASE severity WHEN '高' THEN 0 WHEN '中' THEN 1 ELSE 2 END,
              recorded_at DESC
            LIMIT 3
            """,
            (client_id,),
        ).fetchall()
        agreements = con.execute(
            """
            SELECT agreement_type, title, summary, status, expiry_date
            FROM client_agreements
            WHERE client_id=? AND status IN ('有効', '確認中', '未締結')
            ORDER BY
              CASE agreement_type WHEN 'NDA' THEN 0 WHEN '掲載可否' THEN 1 WHEN '支払条件' THEN 2 ELSE 3 END,
              expiry_date = '',
              expiry_date
            LIMIT 5
            """,
            (client_id,),
        ).fetchall()
    c = dict(client)
    return {
        "client_id": c["id"],
        "client_name": c["client_name"],
        "risk_level": c.get("risk_level", "通常"),
        "payment_terms": c.get("payment_terms", ""),
        "summary_note": c.get("summary_note", ""),
        "notes": [dict(r) for r in notes],
        "agreements": [dict(r) for r in agreements],
    }


def get_client_alert_by_name(client_name: str) -> dict:
    if not client_name:
        return {}
    with _conn() as con:
        client = con.execute("SELECT * FROM clients WHERE client_name=?", (client_name.strip(),)).fetchone()
        if not client:
            client = con.execute("SELECT * FROM clients WHERE aliases LIKE ?", (f"%{client_name.strip()}%",)).fetchone()
        if not client:
            return {}
        client_id = client["id"]
    return get_client_alert(client_id)


def get_company_names() -> list:
    with _conn() as con:
        rows = con.execute(
            "SELECT DISTINCT kaisha_mei FROM engagements "
            "WHERE kaisha_mei != '' ORDER BY kaisha_mei"
        ).fetchall()
    return [r[0] for r in rows]


def next_uketsuke_no() -> str:
    yy = datetime.now().strftime("%y")
    prefix = f"A{yy}"
    with _conn() as con:
        rows = con.execute(
            "SELECT uketsuke_no FROM engagements WHERE uketsuke_no LIKE ?", (f"{prefix}%",)
        ).fetchall()
    existing = []
    for r in rows:
        m = re.fullmatch(rf"A{yy}(\d+)", r[0])
        if m:
            existing.append(int(m.group(1)))
    seq = max(existing) + 1 if existing else 1
    return f"{prefix}{seq:03d}"


def next_anken_no(kind: str = "M") -> str:
    yy = datetime.now().strftime("%y")
    prefix = f"{kind}{yy}"
    with _conn() as con:
        rows = con.execute(
            "SELECT anken_no FROM engagements WHERE anken_no LIKE ?", (f"{prefix}%",)
        ).fetchall()
    existing = []
    for r in rows:
        m = re.fullmatch(rf"{kind}{yy}(\d+)", r[0])
        if m:
            existing.append(int(m.group(1)))
    seq = max(existing) + 1 if existing else 1
    return f"{prefix}{seq:03d}"


def export_to_xlsx(path):
    import pandas as pd
    with _conn() as con:
        inquiry_rows = con.execute(
            "SELECT * FROM engagements WHERE phase='inquiry' ORDER BY uketsuke_no"
        ).fetchall()
        project_rows = con.execute(
            "SELECT * FROM engagements WHERE phase='project' ORDER BY anken_no"
        ).fetchall()

    def _status(r):
        end = _as_str(r['end_state'])
        return end if end in ('完了', 'ボツ') else _as_str(r['status'])

    uketsuke_data = [{
        '受付No':          _as_str(r['uketsuke_no']),
        '受付日':          _as_str(r['uketsuke_date']),
        '依頼者':          _as_str(r['irai_sha']),
        '会社名':          _as_str(r['kaisha_mei']),
        '連絡先':          _as_str(r['renraku_saki']),
        '件名':            _as_str(r['anken_mei']),
        '問い合わせ内容':  _as_str(r['toiawase']),
        'Co':              _as_str(r['co']),
        'ステータス':      _status(r),
        '案件番号':        _as_str(r['anken_no']),
        '次回連絡日':      _as_str(r['renraku_date']),
        '進捗メモ':        _as_str(r['memo']),
    } for r in inquiry_rows]

    anken_data = [{
        'PID':        str(i + 1),
        '依頼日':     _as_str(r['irai_date']),
        '管理番号':   _as_str(r['anken_no']),
        '依頼者':     _as_str(r['irai_sha']),
        '案件名':     _as_str(r['anken_mei']),
        '金額':       _as_str(r['kingaku']),
        '受注日':     _as_str(r['juchu_date']),
        '確認日':     _as_str(r['kakunin_date']),
        '納期':       _as_str(r['nouki']),
        '作業完了日': _as_str(r['sagyou_kanryo']),
        '入金':       _as_str(r['nyukin']),
        'Co':         _as_str(r['co']),
        'ボツの理由': _as_str(r['botsu_riyu']),
        '受付No':     _as_str(r['uketsuke_no']),
        'ステータス': _status(r),
        '会社名':     _as_str(r['kaisha_mei']),
        '修正回数':   _as_str(r['revision_count']) or '0',
        '進捗メモ':   _as_str(r['memo']),
    } for i, r in enumerate(project_rows)]

    with pd.ExcelWriter(str(path), engine='openpyxl') as w:
        pd.DataFrame(uketsuke_data).to_excel(w, sheet_name='受付管理', index=False)
        pd.DataFrame(anken_data).to_excel(w, sheet_name='案件管理', index=False)