Project_Fit.py

from __future__ import annotations
import os
from datetime import date
from typing import Dict, Optional, List, Tuple

import pandas as pd
import plotly.express as px
import streamlit as st

# ------------------------------
# 基本設定・CSVパス
# ------------------------------
st.set_page_config(page_title="案件アサイン管理", layout="wide")
DATA_DIR = "."
MEMBERS_CSV = os.path.join(DATA_DIR, "members.csv")
PROJECTS_CSV = os.path.join(DATA_DIR, "projects.csv")
ASSIGN_CSV = os.path.join(DATA_DIR, "assignments.csv")

# ------------------------------
# ユーティリティ
# ------------------------------
def _ensure_dir(path: str):
    d = os.path.dirname(path)
    if d and not os.path.exists(d):
        os.makedirs(d, exist_ok=True)

def _date_or_none(x) -> Optional[date]:
    try:
        return pd.to_datetime(x, errors="coerce").date()
    except Exception:
        return None

def parse_skill_str(s: str) -> Dict[str, int]:
    """
    "Python(3); Flask(2)" -> {"Python":3, "Flask":2}
    """
    out: Dict[str, int] = {}
    if not s:
        return out
    for p in s.replace("；", ";").split(";"):
        p = p.strip()
        if not p:
            continue
        if "(" in p and p.endswith(")"):
            name = p[: p.rfind("(")].strip()
            try:
                lv = int(p[p.rfind("(")+1:-1].strip())
            except Exception:
                lv = 1
        else:
            name, lv = p, 1
        if name:
            out[name] = max(1, int(lv))
    return out

def format_skill_str(d: Dict[str, int]) -> str:
    return "; ".join([f"{k}({v})" for k, v in d.items()]) if d else ""

def match_score(member_skills: Dict[str, int], req: Dict[str, int]) -> Tuple[bool, int, int]:
    if not req:
        return True, 0, 0
    ok, matched, score = True, 0, 0
    for rk, rv in req.items():
        mv = member_skills.get(rk, 0)
        if mv >= rv:
            matched += 1
            score += mv
        else:
            ok = False
    return ok, matched, score

def next_id(df: pd.DataFrame, col="id") -> int:
    return (int(df[col].max()) + 1) if not df.empty else 1

# ------------------------------
# データ読み書き（CSV・キャッシュ）
# ------------------------------
@st.cache_data
def load_members() -> pd.DataFrame:
    if os.path.exists(MEMBERS_CSV):
        df = pd.read_csv(MEMBERS_CSV)
    else:
        df = pd.DataFrame([
            {"id": 1, "name": "佐藤", "skills": "Python(3); Flask(2)", "capacity_per_week": 32},
            {"id": 2, "name": "鈴木", "skills": "Java(3); Spring(2)", "capacity_per_week": 40},
            {"id": 3, "name": "田中", "skills": "Python(2); Pandas(2)", "capacity_per_week": 20},
        ])
        _ensure_dir(MEMBERS_CSV)
        df.to_csv(MEMBERS_CSV, index=False, encoding="utf-8")
    df["id"] = pd.to_numeric(df["id"], errors="coerce").fillna(0).astype(int)
    df["name"] = df["name"].astype(str)
    df["skills"] = df["skills"].fillna("").astype(str)
    df["capacity_per_week"] = pd.to_numeric(df["capacity_per_week"], errors="coerce").fillna(40).astype(int)
    return df

@st.cache_data
def load_projects() -> pd.DataFrame:
    if os.path.exists(PROJECTS_CSV):
        df = pd.read_csv(PROJECTS_CSV)
    else:
        df = pd.DataFrame([
            {"id": 101, "name": "在庫API", "required_skills": "Python(2)", "hours_needed": 40,
             "start_date": "2025-10-01", "end_date": "2025-10-31", "status": "稼働中"},
            {"id": 102, "name": "社内ポータル", "required_skills": "Java(2); Spring(1)", "hours_needed": 60,
             "start_date": "2025-10-05", "end_date": "2025-11-15", "status": "提案中"},
        ])
        _ensure_dir(PROJECTS_CSV)
        df.to_csv(PROJECTS_CSV, index=False, encoding="utf-8")
    df["id"] = pd.to_numeric(df["id"], errors="coerce").fillna(0).astype(int)
    df["name"] = df["name"].astype(str)
    df["required_skills"] = df["required_skills"].fillna("").astype(str)
    df["hours_needed"] = pd.to_numeric(df["hours_needed"], errors="coerce").fillna(0).astype(int)
    df["start_date"] = pd.to_datetime(df["start_date"], errors="coerce").dt.date
    df["end_date"] = pd.to_datetime(df["end_date"], errors="coerce").dt.date
    df["status"] = df["status"].fillna("").astype(str)
    return df

@st.cache_data
def load_assignments() -> pd.DataFrame:
    if os.path.exists(ASSIGN_CSV):
        df = pd.read_csv(ASSIGN_CSV)
    else:
        df = pd.DataFrame(columns=["project_id", "member_id", "hours"])
        _ensure_dir(ASSIGN_CSV)
        df.to_csv(ASSIGN_CSV, index=False, encoding="utf-8")
    for c in ["project_id", "member_id", "hours"]:
        df[c] = pd.to_numeric(df[c], errors="coerce").fillna(0).astype(int)
    return df

def save_members(df: pd.DataFrame):
    df.to_csv(MEMBERS_CSV, index=False, encoding="utf-8")
    st.cache_data.clear()

def save_projects(df: pd.DataFrame):
    df.to_csv(PROJECTS_CSV, index=False, encoding="utf-8")
    st.cache_data.clear()

def save_assignments(df: pd.DataFrame):
    df.to_csv(ASSIGN_CSV, index=False, encoding="utf-8")
    st.cache_data.clear()

# ------------------------------
# 計算ヘルパ
# ------------------------------
def remaining_project_hours(pid: int, projects: pd.DataFrame, assignments: pd.DataFrame) -> int:
    p = projects.loc[projects["id"] == pid]
    if p.empty:
        return 0
    need = int(p["hours_needed"].iloc[0])
    used = int(assignments.loc[assignments["project_id"] == pid, "hours"].sum())
    return max(0, need - used)

def member_used_hours(mid: int, assignments: pd.DataFrame) -> int:
    return int(assignments.loc[assignments["member_id"] == mid, "hours"].sum())

def member_remaining_capacity(mid: int, members: pd.DataFrame, assignments: pd.DataFrame) -> int:
    m = members.loc[members["id"] == mid]
    if m.empty:
        return 0
    cap = int(m["capacity_per_week"].iloc[0])
    used = member_used_hours(mid, assignments)
    return max(0, cap - used)

# ------------------------------
# 自動アサイン（必要スキルレベルを満たす人を優先）
# ------------------------------
def auto_assign(projects: pd.DataFrame, members: pd.DataFrame, assignments: pd.DataFrame,
                max_chunk_hours: int, max_total_per_member_per_project: int) -> pd.DataFrame:
    def proj_key(row):
        req = parse_skill_str(row["required_skills"])
        need_left = remaining_project_hours(int(row["id"]), projects, assignments)
        return (-need_left, -len(req))

    projs = projects.copy()
    projs["__k"] = projs.apply(proj_key, axis=1)
    projs = projs.sort_values("__k").drop(columns=["__k"])

    for _, prow in projs.iterrows():
        pid = int(prow["id"])
        need = remaining_project_hours(pid, projects, assignments)
        if need <= 0:
            continue
        req = parse_skill_str(prow["required_skills"])

        cands: List[Tuple[int,int,int,int,int]] = []  # (mid, rem, matched, score, used_on_proj)
        for _, mrow in members.iterrows():
            mid = int(mrow["id"])
            mskills = parse_skill_str(mrow["skills"])
            ok, matched, score = match_score(mskills, req)
            rem = member_remaining_capacity(mid, members, assignments)
            used_on_proj = int(assignments[(assignments["project_id"] == pid) &
                                           (assignments["member_id"] == mid)]["hours"].sum())
            if ok and rem > 0 and used_on_proj < max_total_per_member_per_project:
                cands.append((mid, rem, matched, score, used_on_proj))
        cands.sort(key=lambda x: (x[3], x[1], x[2]), reverse=True)

        for (mid, rem, matched, score, used_on_proj) in cands:
            if need <= 0:
                break
            proj_room = max_total_per_member_per_project - used_on_proj
            if proj_room <= 0: 
                continue
            take = min(need, rem, max_chunk_hours, proj_room)
            if take <= 0:
                continue
            assignments = pd.concat([assignments, pd.DataFrame([{
                "project_id": pid, "member_id": mid, "hours": int(take)
            }])], ignore_index=True)
            need -= take

    return assignments

# ------------------------------
# UI
# ------------------------------
st.title("🧩 案件アサイン管理")

with st.sidebar:
    st.header("⚙️ アサイン設定")
    max_chunk_hours = st.slider("1回の割当 上限[h]", 1, 16, 8, 1)
    max_total_per_member_per_project = st.slider("1人あたり/1案件の上限[h]", 4, 80, 40, 4)
    st.caption("※ 自動アサイン時の安全装置。偏りと過負荷を抑えます。")

members = load_members()
projects = load_projects()
assignments = load_assignments()

col1, col2, col3 = st.columns([1.2, 1.2, 1.1])

# ------------------------------ プロジェクト：追加＋編集/削除 ------------------------------
with col1:
    st.subheader("🗂️ プロジェクト")

    with st.expander("新規追加", expanded=False):
        pname = st.text_input("プロジェクト名", key="pname")
        preq_names = st.text_input("必要スキル名（;区切り）", placeholder="Java; Spring; SQL")
        preq_levels = st.text_input("必要スキルレベル（;区切り 同数）", placeholder="2; 1; 2")
        phours = st.number_input("総工数[h]", min_value=1, step=1, value=40)
        pstart = st.date_input("開始日", value=_date_or_none("2025-10-01") or date.today())
        pend = st.date_input("終了日", value=_date_or_none("2025-10-31") or date.today())
        pstatus = st.selectbox("ステータス", ["提案中", "稼働中", "完了"], index=1)
        if st.button("追加する", key="add_project"):
            if not pname.strip():
                st.warning("プロジェクト名を入力してください")
            else:
                names = [x.strip() for x in preq_names.replace("；",";").split(";") if x.strip()]
                levels = [x.strip() for x in preq_levels.replace("；",";").split(";") if x.strip()]
                if names and (len(names) != len(levels)):
                    st.warning("スキル名とレベルの数を合わせてください")
                else:
                    req_map: Dict[str,int] = {}
                    for i, nm in enumerate(names):
                        try:
                            lv = int(levels[i])
                        except Exception:
                            lv = 1
                        req_map[nm] = max(1, lv)
                    req_str = format_skill_str(req_map)
                    pid = next_id(projects, "id")
                    row = pd.DataFrame([{
                        "id": pid, "name": pname.strip(), "required_skills": req_str,
                        "hours_needed": int(phours), "start_date": pstart, "end_date": pend, "status": pstatus
                    }])
                    projects = pd.concat([projects, row], ignore_index=True)
                    save_projects(projects)
                    st.success("📁 プロジェクトを追加しました")

    # 一覧（編集・削除対応）
    disp = projects.copy()
    disp["unassigned"] = disp["id"].apply(lambda x: remaining_project_hours(int(x), projects, assignments))
    disp["削除"] = False
    edited = st.data_editor(
        disp[["id","name","required_skills","hours_needed","unassigned","start_date","end_date","status","削除"]],
        use_container_width=True, hide_index=True,
        column_config={
            "id": st.column_config.NumberColumn("id", disabled=True),
            "name": st.column_config.TextColumn("name"),
            "required_skills": st.column_config.TextColumn("required_skills", help="例: Java(2); Spring(1)"),
            "hours_needed": st.column_config.NumberColumn("hours_needed", min_value=0, step=1),
            "unassigned": st.column_config.NumberColumn("unassigned", disabled=True),
            "start_date": st.column_config.DateColumn("start_date"),
            "end_date": st.column_config.DateColumn("end_date"),
            "status": st.column_config.TextColumn("status"),
            "削除": st.column_config.CheckboxColumn("削除", default=False),
        },
        key="proj_editor",
    )
    if st.button("💾 プロジェクト変更を保存"):
        try:
            df2 = edited.copy()
            df2["削除"] = df2["削除"].fillna(False).astype(bool)
            keep = df2.loc[~df2["削除"]].copy()
            # 型整形
            keep["start_date"] = pd.to_datetime(keep["start_date"], errors="coerce").dt.date
            keep["end_date"]   = pd.to_datetime(keep["end_date"], errors="coerce").dt.date
            keep = keep[["id","name","required_skills","hours_needed","start_date","end_date","status"]]
            save_projects(keep)
            projects = keep
            # 参照切れアサインを落とす
            valid_pids = set(projects["id"].astype(int))
            assignments = load_assignments()
            assignments = assignments[assignments["project_id"].isin(valid_pids)]
            save_assignments(assignments)
            st.success("保存しました")
        except Exception as e:
            st.error(f"保存時にエラー：{e}")

# ------------------------------ メンバー：追加＋編集/削除 ------------------------------
with col2:
    st.subheader("👥 メンバー")

    with st.expander("新規追加", expanded=False):
        mname = st.text_input("氏名", key="mname")
        skill_names = st.text_input("保有スキル名（;区切り）", placeholder="Python; Flask; AWS")
        skill_levels = st.text_input("スキルレベル（;区切り 同数）", placeholder="3; 2; 1")
        mcap = st.number_input("週のキャパ[h]", min_value=0, step=1, value=40, key="mcap")
        if st.button("追加する", key="add_member"):
            if not mname.strip():
                st.warning("氏名を入力してください")
            else:
                names = [x.strip() for x in skill_names.replace("；",";").split(";") if x.strip()]
                levels = [x.strip() for x in skill_levels.replace("；",";").split(";") if x.strip()]
                if names and (len(names) != len(levels)):
                    st.warning("スキル名とレベルの数を合わせてください")
                else:
                    mp: Dict[str,int] = {}
                    for i, nm in enumerate(names):
                        try:
                            lv = int(levels[i])
                        except Exception:
                            lv = 1
                        mp[nm] = max(1, lv)
                    mid = next_id(members, "id")
                    row = pd.DataFrame([{
                        "id": mid, "name": mname.strip(), "skills": format_skill_str(mp),
                        "capacity_per_week": int(mcap)
                    }])
                    members = pd.concat([members, row], ignore_index=True)
                    save_members(members)
                    st.success("🙋 メンバーを追加しました")

    # 一覧（編集・削除対応）
    mem = members.copy()
    mem["used"] = mem["id"].apply(lambda x: member_used_hours(int(x), assignments))
    mem["remaining"] = mem.apply(lambda r: member_remaining_capacity(int(r["id"]), members, assignments), axis=1)
    mem["削除"] = False
    mem_edited = st.data_editor(
        mem[["id","name","skills","capacity_per_week","used","remaining","削除"]],
        use_container_width=True, hide_index=True,
        column_config={
            "id": st.column_config.NumberColumn("id", disabled=True),
            "name": st.column_config.TextColumn("name"),
            "skills": st.column_config.TextColumn("skills", help="例: Python(3); Flask(2)"),
            "capacity_per_week": st.column_config.NumberColumn("capacity_per_week", min_value=0, step=1),
            "used": st.column_config.NumberColumn("used", disabled=True),
            "remaining": st.column_config.NumberColumn("remaining", disabled=True),
            "削除": st.column_config.CheckboxColumn("削除", default=False),
        },
        key="mem_editor",
    )
    if st.button("💾 メンバー変更を保存"):
        try:
            df2 = mem_edited.copy()
            df2["削除"] = df2["削除"].fillna(False).astype(bool)
            keep = df2.loc[~df2["削除"]].copy()
            keep = keep[["id","name","skills","capacity_per_week"]]
            save_members(keep)
            members = keep
            # 参照切れアサインを落とす
            valid_mids = set(members["id"].astype(int))
            assignments = load_assignments()
            assignments = assignments[assignments["member_id"].isin(valid_mids)]
            save_assignments(assignments)
            st.success("保存しました")
        except Exception as e:
            st.error(f"保存時にエラー：{e}")

# ------------------------------ アサイン：自動・手動・編集/削除 ------------------------------
with col3:
    st.subheader("🧩 アサイン")
    c1, c2 = st.columns(2)
    with c1:
        if st.button("⚙️ 自動アサインを実行"):
            assignments = auto_assign(projects, members, assignments,
                                      max_chunk_hours=max_chunk_hours,
                                      max_total_per_member_per_project=max_total_per_member_per_project)
            save_assignments(assignments)
            st.success("自動アサインを行いました")
    with c2:
        if st.button("🧹 アサインを全クリア"):
            assignments = assignments.iloc[0:0].copy()
            save_assignments(assignments)
            st.info("アサインをクリアしました")

    with st.expander("➕ 手動で1行追加", expanded=False):
        proj_opts = {f'{row["name"]} (ID:{row["id"]})': int(row["id"]) for _, row in projects.iterrows()}
        mem_opts  = {f'{row["name"]} (ID:{row["id"]})': int(row["id"]) for _, row in members.iterrows()}
        sel_p = st.selectbox("プロジェクト", list(proj_opts.keys())) if proj_opts else None
        sel_m = st.selectbox("メンバー", list(mem_opts.keys())) if mem_opts else None
        add_h = st.number_input("割当[h]", 1, 80, 4, 1)
        if st.button("追加する", key="add_assign"):
            if not (sel_p and sel_m):
                st.warning("プロジェクトとメンバーを選択してください")
            else:
                pid, mid = proj_opts[sel_p], mem_opts[sel_m]
                need_left = remaining_project_hours(pid, projects, assignments)
                rem_cap = member_remaining_capacity(mid, members, assignments)
                used_on_proj = int(assignments[(assignments["project_id"] == pid) &
                                               (assignments["member_id"] == mid)]["hours"].sum())
                proj_room = max_total_per_member_per_project - used_on_proj
                take = min(int(add_h), need_left if need_left>0 else int(add_h), rem_cap, proj_room)
                if take <= 0:
                    st.warning("⚠️ 追加できません（未充足0/キャパ0/上限超過）")
                else:
                    assignments = pd.concat([assignments, pd.DataFrame([{
                        "project_id": pid, "member_id": mid, "hours": int(take)
                    }])], ignore_index=True)
                    save_assignments(assignments)
                    st.success("追加しました")

    # 編集・削除テーブル
    if assignments.empty:
        st.info("アサインがありません")
    else:
        id_to_proj = {int(r["id"]): str(r["name"]) for _, r in projects.iterrows()}
        id_to_mem  = {int(r["id"]): str(r["name"]) for _, r in members.iterrows()}
        disp = assignments.copy().reset_index(drop=True)
        disp["project_name"] = disp["project_id"].map(id_to_proj).fillna("(削除済み)")
        disp["member_name"]  = disp["member_id"].map(id_to_mem).fillna("(削除済み)")
        disp = disp.rename(columns={"hours":"hours(h)"})
        disp["削除"] = False

        edited = st.data_editor(
            disp[["project_id","project_name","member_id","member_name","hours(h)","削除"]],
            use_container_width=True, hide_index=True,
            column_config={
                "project_id": st.column_config.NumberColumn("project_id"),
                "project_name": st.column_config.TextColumn("project_name", disabled=True),
                "member_id": st.column_config.NumberColumn("member_id"),
                "member_name": st.column_config.TextColumn("member_name", disabled=True),
                "hours(h)": st.column_config.NumberColumn("hours(h)", min_value=0, step=1),
                "削除": st.column_config.CheckboxColumn("削除", default=False),
            },
            key="assign_editor_v2",
        )

        if st.button("💾 アサイン変更を保存"):
            try:
                df2 = edited.copy()
                # 空行・不正値除外
                df2["project_id"] = pd.to_numeric(df2["project_id"], errors="coerce")
                df2["member_id"]  = pd.to_numeric(df2["member_id"], errors="coerce")
                df2["hours(h)"]   = pd.to_numeric(df2["hours(h)"], errors="coerce").fillna(0).astype(int)
                df2 = df2.dropna(subset=["project_id","member_id"]).copy()
                df2["project_id"] = df2["project_id"].astype(int)
                df2["member_id"]  = df2["member_id"].astype(int)
                df2["削除"]       = df2["削除"].fillna(False).astype(bool)
                df2 = df2.loc[~df2["削除"]].copy()

                # 参照整合性チェック
                valid_pids = set(projects["id"].astype(int))
                valid_mids = set(members["id"].astype(int))
                df2 = df2[df2["project_id"].isin(valid_pids) & df2["member_id"].isin(valid_mids)]

                out = df2.rename(columns={"hours(h)":"hours"})[["project_id","member_id","hours"]]
                save_assignments(out)
                assignments = out
                st.success("保存しました")
            except Exception as e:
                st.error(f"保存時にエラー：{e}")

# ------------------------------ 個人稼働（バー）＆警告 ------------------------------
st.subheader("📊 個人稼働（合計割当）")
if not members.empty:
    rows = []
    for _, m in members.iterrows():
        mid = int(m["id"])
        used = member_used_hours(mid, assignments)
        cap = int(m["capacity_per_week"])
        rows.append({"member": m["name"], "used": used, "capacity": cap})
    ldf = pd.DataFrame(rows)
    if not ldf.empty:
        st.bar_chart(ldf.set_index("member")["used"])
        over = ldf[ldf["used"] > ldf["capacity"]]
        if not over.empty:
            st.error("⚠️ キャパ超過: " + ", ".join(over["member"].tolist()))

# ------------------------------ ガントチャート ------------------------------
st.subheader("🗓️ ガントチャート")
if projects.empty or assignments.empty:
    st.info("プロジェクトとアサインが必要です")
else:
    proj_map = projects.set_index("id")[["name","start_date","end_date"]].to_dict(orient="index")
    mem_map  = members.set_index("id")["name"].to_dict()
    rows = []
    for _, a in assignments.iterrows():
        pid, mid, h = int(a["project_id"]), int(a["member_id"]), int(a["hours"])
        if pid not in proj_map or mid not in mem_map:
            continue
        s = proj_map[pid]["start_date"]
        e = proj_map[pid]["end_date"]
        if pd.isna(s) or pd.isna(e):
            continue
        rows.append({
            "担当者": mem_map.get(mid, f"ID:{mid}"),
            "プロジェクト": proj_map[pid]["name"],
            "開始": s, "終了": e, "時間[h]": h
        })
    if rows:
        gdf = pd.DataFrame(rows)
        # 配色はデフォルト
        fig = px.timeline(
            gdf, x_start="開始", x_end="終了", y="担当者", color="プロジェクト",
            hover_data=["時間[h]"],
            title="人×案件×期間（プロジェクト期間に準拠）"
        )
        fig.update_yaxes(autorange="reversed")
        st.plotly_chart(fig, use_container_width=True)
    else:
        st.info("ガントに表示できるデータがありません（期間未設定など）")

# ------------------------------ エクスポート ------------------------------
st.divider()
st.download_button("📥 members.csv をダウンロード", members.to_csv(index=False).encode("utf-8"),
                   file_name="members.csv", mime="text/csv", use_container_width=True)
st.download_button("📥 projects.csv をダウンロード", projects.to_csv(index=False).encode("utf-8"),
                   file_name="projects.csv", mime="text/csv", use_container_width=True)
st.download_button("📥 assignments.csv をダウンロード", assignments.to_csv(index=False).encode("utf-8"),
                   file_name="assignments.csv", mime="text/csv", use_container_width=True)