main.py

# StockFlow — 更新時間：2026-07-24 15:08
"""
StockFlow — FastAPI 後端 v3.2（多店管理 Phase 3）
新增：LINE 群組指令「綁定 店代碼」／「解除綁定」，讓群組自行綁定要接收哪家店的日報/週報
新增：「狀態」指令會顯示目前綁定的店家名稱；未綁定時提示如何綁定
沿用：admin→superadmin 一次性遷移保護、PUT /users/{username}（Phase 2 hotfix）
沿用：products / inventory / consumption / orders / staff / categories / line_groups
      全部依登入者所屬的 store_id 過濾，各店資料完全獨立（Phase 2）
沿用：多店（stores）資料表、superadmin/admin/staff 三層權限、店家管理 API（Phase 1）
"""

import os, csv, io, hashlib, hmac, base64
import bcrypt as _bcrypt
from datetime import datetime, timedelta, timezone
from typing import Optional

import psycopg2
import psycopg2.extras
from fastapi import FastAPI, Depends, HTTPException, Request, Header, BackgroundTasks
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import StreamingResponse, JSONResponse
from fastapi.security import HTTPBearer, HTTPAuthorizationCredentials
from pydantic import BaseModel
from jose import JWTError, jwt
from dotenv import load_dotenv

# LINE Bot SDK v3
from linebot.v3 import WebhookParser
from linebot.v3.exceptions import InvalidSignatureError
from linebot.v3.messaging import (
    Configuration, ApiClient, MessagingApi,
    ReplyMessageRequest, PushMessageRequest, TextMessage,
)
from linebot.v3.webhooks import (
    MessageEvent, JoinEvent, LeaveEvent,
    TextMessageContent, GroupSource, RoomSource, UserSource,
)

load_dotenv()

DATABASE_URL        = os.getenv("DATABASE_URL")
JWT_SECRET          = os.getenv("JWT_SECRET", "change-me")
JWT_ALGORITHM       = "HS256"
JWT_EXPIRE_MIN      = int(os.getenv("JWT_EXPIRE_MIN", 480))
INIT_ADMIN_USER     = os.getenv("ADMIN_USER", "admin")
INIT_ADMIN_PASS     = os.getenv("ADMIN_PASS_PLAIN", "changeme")
CORS_ORIGINS        = os.getenv("CORS_ORIGINS", "*").split(",")
LINE_TOKEN          = os.getenv("LINE_CHANNEL_ACCESS_TOKEN", "")
LINE_SECRET         = os.getenv("LINE_CHANNEL_SECRET", "")

_line_config  = Configuration(access_token=LINE_TOKEN) if LINE_TOKEN else None
_line_parser  = WebhookParser(LINE_SECRET) if LINE_SECRET else None

def get_line_api():
    if not _line_config:
        return None
    return MessagingApi(ApiClient(_line_config))

app = FastAPI(title="StockFlow API", version="3.1.0")
_cors_origins = CORS_ORIGINS if CORS_ORIGINS != ["*"] else ["*"]
_allow_creds   = "*" not in _cors_origins

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=False,
    allow_methods=["*"],
    allow_headers=["*"],
)

# ── bcrypt helpers ────────────────────────────────────────────
def hash_password(plain: str) -> str:
    encoded = plain.encode("utf-8")[:72]
    return _bcrypt.hashpw(encoded, _bcrypt.gensalt(rounds=12)).decode("utf-8")

def verify_password(plain: str, hashed: str) -> bool:
    try:
        return _bcrypt.checkpw(plain.encode("utf-8")[:72], hashed.encode("utf-8"))
    except Exception:
        return False

# ── JWT ──────────────────────────────────────────────────────
bearer = HTTPBearer()

def create_token(username: str, role: str, store_id: Optional[int]) -> str:
    exp = datetime.now(timezone.utc) + timedelta(minutes=JWT_EXPIRE_MIN)
    return jwt.encode(
        {"sub": username, "role": role, "store_id": store_id, "exp": exp},
        JWT_SECRET, algorithm=JWT_ALGORITHM,
    )

def decode_token(creds: HTTPAuthorizationCredentials = Depends(bearer)) -> dict:
    try:
        payload  = jwt.decode(creds.credentials, JWT_SECRET, algorithms=[JWT_ALGORITHM])
        username = payload.get("sub")
        role     = payload.get("role", "staff")
        store_id = payload.get("store_id")
        if not username:
            raise HTTPException(status_code=401, detail="Token 無效")
        return {"username": username, "role": role, "store_id": store_id}
    except JWTError:
        raise HTTPException(status_code=401, detail="Token 過期或無效")

def get_current_user(td: dict = Depends(decode_token)) -> str:
    return td["username"]

def require_admin(td: dict = Depends(decode_token)) -> dict:
    """店級管理員權限：admin（限自己店）或 superadmin（不限店）"""
    if td["role"] not in ("admin", "superadmin"):
        raise HTTPException(status_code=403, detail="此操作需要管理員權限")
    return td

def require_superadmin(td: dict = Depends(decode_token)) -> dict:
    """僅限總管理員（跨店操作，如店家管理）"""
    if td["role"] != "superadmin":
        raise HTTPException(status_code=403, detail="此操作僅限總管理員")
    return td

def _resolve_active_store_id(td: dict, x_store_id: Optional[str]) -> int:
    """
    依角色解析出「這次請求實際要操作哪個 store_id」：
    - admin/staff：一律用自己所屬的 store_id，忽略 header（避免跨店存取）
    - superadmin：不綁店，必須帶 X-Store-Id header 指定要操作的店
    """
    if td["role"] == "superadmin":
        if not x_store_id:
            raise HTTPException(status_code=400, detail="總管理員需指定 X-Store-Id 才能操作店家資料")
        try:
            store_id = int(x_store_id)
        except ValueError:
            raise HTTPException(status_code=400, detail="X-Store-Id 格式錯誤")
        conn = get_conn(); cur = conn.cursor()
        cur.execute("SELECT id FROM stores WHERE id=%s", (store_id,))
        exists = cur.fetchone()
        cur.close(); conn.close()
        if not exists:
            raise HTTPException(status_code=404, detail="找不到指定的店家")
        return store_id
    store_id = td["store_id"]
    if not store_id:
        raise HTTPException(status_code=400, detail="此帳號尚未歸屬任何店家，請聯絡總管理員")
    return store_id

def get_active_store(
    td: dict = Depends(decode_token),
    x_store_id: Optional[str] = Header(None, alias="X-Store-Id"),
) -> dict:
    """一般業務 API 用：任何登入角色皆可，回傳 {store_id, username, role}"""
    store_id = _resolve_active_store_id(td, x_store_id)
    return {"store_id": store_id, "username": td["username"], "role": td["role"]}

def require_store_admin(
    td: dict = Depends(require_admin),
    x_store_id: Optional[str] = Header(None, alias="X-Store-Id"),
) -> dict:
    """管理級業務 API 用：admin 或 superadmin，且已解析出要操作的 store_id"""
    store_id = _resolve_active_store_id(td, x_store_id)
    return {"store_id": store_id, "username": td["username"], "role": td["role"]}

def resolve_store_id(td: dict, requested_store_id: Optional[int] = None) -> Optional[int]:
    """舊版相容輔助函式，保留給尚未改寫的呼叫端使用"""
    if td["role"] == "superadmin":
        return requested_store_id
    return td["store_id"]

# ── DB ───────────────────────────────────────────────────────
def get_conn():
    return psycopg2.connect(DATABASE_URL, cursor_factory=psycopg2.extras.RealDictCursor)

def init_db():
    conn = get_conn(); cur = conn.cursor()

    cur.execute("SELECT pg_advisory_lock(123456789)")

    # ── 一次性遷移記錄表：確保下面標記為「僅執行一次」的遷移不會在每次重啟時重跑 ──
    cur.execute("""
        CREATE TABLE IF NOT EXISTS schema_migrations (
            name       TEXT PRIMARY KEY,
            applied_at TIMESTAMPTZ DEFAULT NOW()
        )
    """)

    # ── 店家（stores）── 多店管理核心 ──────────────────────────
    cur.execute("""
        CREATE TABLE IF NOT EXISTS stores (
            id         SERIAL PRIMARY KEY,
            name       TEXT NOT NULL,
            code       TEXT UNIQUE NOT NULL,
            active     BOOLEAN NOT NULL DEFAULT TRUE,
            created_at TIMESTAMPTZ DEFAULT NOW()
        )
    """)
    cur.execute("SELECT COUNT(*) AS n FROM stores")
    if cur.fetchone()["n"] == 0:
        cur.execute(
            "INSERT INTO stores (name, code) VALUES (%s,%s) ON CONFLICT DO NOTHING",
            ("預設店", "DEFAULT")
        )
    cur.execute("SELECT id FROM stores ORDER BY id ASC LIMIT 1")
    default_store_id = cur.fetchone()["id"]

    # ── users ───────────────────────────────────────────────────
    cur.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id            SERIAL PRIMARY KEY,
            username      TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            role          TEXT NOT NULL DEFAULT 'staff'
                              CHECK (role IN ('superadmin','admin','staff')),
            store_id      INTEGER REFERENCES stores(id) ON DELETE SET NULL,
            created_at    TIMESTAMPTZ DEFAULT NOW()
        )
    """)
    cur.execute("ALTER TABLE users ADD COLUMN IF NOT EXISTS store_id INTEGER REFERENCES stores(id) ON DELETE SET NULL")

    cur.execute("""
        DO $$
        BEGIN
            IF EXISTS (
                SELECT 1 FROM pg_constraint
                WHERE conname = 'users_role_check' AND conrelid = 'users'::regclass
            ) THEN
                ALTER TABLE users DROP CONSTRAINT users_role_check;
            END IF;
            ALTER TABLE users ADD CONSTRAINT users_role_check
                CHECK (role IN ('superadmin','admin','staff'));
        END$$
    """)

    # ★ 修正：這是 Phase 1 升級時的「一次性」遷移（把升級前的舊 admin 帳號轉正為 superadmin），
    # 過去每次重啟都會重跑，導致之後新建的店級 admin 帳號被誤升級。現在用 schema_migrations 鎖住只跑一次。
    cur.execute("SELECT 1 FROM schema_migrations WHERE name='v3_admin_to_superadmin_once'")
    if not cur.fetchone():
        cur.execute("UPDATE users SET role='superadmin', store_id=NULL WHERE role='admin'")
        cur.execute("INSERT INTO schema_migrations (name) VALUES ('v3_admin_to_superadmin_once')")
    cur.execute("UPDATE users SET store_id=%s WHERE role='staff' AND store_id IS NULL", (default_store_id,))

    cur.execute("SELECT COUNT(*) AS n FROM users WHERE role='superadmin'")
    if cur.fetchone()["n"] == 0:
        init_pass = INIT_ADMIN_PASS
        if init_pass.startswith("$2") or len(init_pass.encode()) > 72:
            import logging
            logging.warning("ADMIN_PASS_PLAIN 無效，改用預設密碼 'changeme'")
            init_pass = "changeme"
        cur.execute(
            "INSERT INTO users (username,password_hash,role,store_id) VALUES (%s,%s,'superadmin',NULL) ON CONFLICT DO NOTHING",
            (INIT_ADMIN_USER, hash_password(init_pass))
        )

    # ── products ──────────────────────────────────────────────
    cur.execute("""
        CREATE TABLE IF NOT EXISTS products (
            id                 SERIAL PRIMARY KEY,
            name               TEXT NOT NULL,
            unit               TEXT NOT NULL DEFAULT '瓶',
            unit_price         NUMERIC(10,2) NOT NULL DEFAULT 0,
            active             BOOLEAN NOT NULL DEFAULT TRUE,
            default_shelf_days INTEGER,
            min_threshold      INTEGER NOT NULL DEFAULT 0,
            category           TEXT,
            created_at         TIMESTAMPTZ DEFAULT NOW()
        )
    """)
    cur.execute("ALTER TABLE products ADD COLUMN IF NOT EXISTS default_shelf_days INTEGER")
    cur.execute("ALTER TABLE products ADD COLUMN IF NOT EXISTS min_threshold INTEGER NOT NULL DEFAULT 0")
    cur.execute("ALTER TABLE products ADD COLUMN IF NOT EXISTS category TEXT")
    cur.execute("ALTER TABLE products ADD COLUMN IF NOT EXISTS store_id INTEGER REFERENCES stores(id) ON DELETE SET NULL")
    cur.execute("UPDATE products SET store_id=%s WHERE store_id IS NULL", (default_store_id,))
    # ★ Phase 2：品項唯一性改成「同店內不可重複」，不同店可以同名
    cur.execute("""
        DO $$
        BEGIN
            IF EXISTS (
                SELECT 1 FROM pg_constraint
                WHERE conname = 'products_name_key' AND conrelid = 'products'::regclass
            ) THEN
                ALTER TABLE products DROP CONSTRAINT products_name_key;
            END IF;
            IF NOT EXISTS (
                SELECT 1 FROM pg_constraint
                WHERE conname = 'products_store_id_name_key' AND conrelid = 'products'::regclass
            ) THEN
                ALTER TABLE products ADD CONSTRAINT products_store_id_name_key UNIQUE (store_id, name);
            END IF;
        END$$
    """)

    cur.execute("""
        CREATE TABLE IF NOT EXISTS inventory_records (
            id             SERIAL PRIMARY KEY,
            product_id     INTEGER REFERENCES products(id) ON DELETE SET NULL,
            recorded_date  DATE        NOT NULL,
            min_threshold  INTEGER     NOT NULL DEFAULT 0,
            order_date     DATE,
            staff_name     TEXT,
            updated_at     TIMESTAMPTZ DEFAULT NOW()
        )
    """)

    cur.execute("""
        ALTER TABLE inventory_records
        ADD COLUMN IF NOT EXISTS product_id INTEGER REFERENCES products(id) ON DELETE SET NULL
    """)
    cur.execute("ALTER TABLE inventory_records ADD COLUMN IF NOT EXISTS store_id INTEGER REFERENCES stores(id) ON DELETE SET NULL")
    cur.execute("UPDATE inventory_records SET store_id=%s WHERE store_id IS NULL", (default_store_id,))
    cur.execute("""
        DO $$
        BEGIN
            IF EXISTS (
                SELECT 1 FROM pg_constraint
                WHERE conname = 'inventory_records_recorded_date_key'
                  AND conrelid = 'inventory_records'::regclass
            ) THEN
                ALTER TABLE inventory_records
                DROP CONSTRAINT inventory_records_recorded_date_key;
            END IF;

            IF NOT EXISTS (
                SELECT 1 FROM pg_constraint
                WHERE conname = 'inventory_records_product_id_recorded_date_key'
                  AND conrelid = 'inventory_records'::regclass
            ) THEN
                ALTER TABLE inventory_records
                ADD CONSTRAINT inventory_records_product_id_recorded_date_key
                UNIQUE (product_id, recorded_date);
            END IF;
        END$$
    """)

    cur.execute("""
        CREATE TABLE IF NOT EXISTS inventory_batches (
            id           SERIAL PRIMARY KEY,
            record_id    INTEGER NOT NULL
                             REFERENCES inventory_records(id) ON DELETE CASCADE,
            expiry_date  DATE    NOT NULL,
            quantity     INTEGER NOT NULL DEFAULT 0
        )
    """)

    cur.execute("""
        CREATE TABLE IF NOT EXISTS staff_settings (
            id   SERIAL PRIMARY KEY,
            name TEXT NOT NULL
        )
    """)
    cur.execute("ALTER TABLE staff_settings ADD COLUMN IF NOT EXISTS store_id INTEGER REFERENCES stores(id) ON DELETE SET NULL")
    cur.execute("UPDATE staff_settings SET store_id=%s WHERE store_id IS NULL", (default_store_id,))
    # ★ Phase 2：人員姓名唯一性改成「同店內不可重複」
    cur.execute("""
        DO $$
        BEGIN
            IF EXISTS (
                SELECT 1 FROM pg_constraint
                WHERE conname = 'staff_settings_name_key' AND conrelid = 'staff_settings'::regclass
            ) THEN
                ALTER TABLE staff_settings DROP CONSTRAINT staff_settings_name_key;
            END IF;
            IF NOT EXISTS (
                SELECT 1 FROM pg_constraint
                WHERE conname = 'staff_settings_store_id_name_key' AND conrelid = 'staff_settings'::regclass
            ) THEN
                ALTER TABLE staff_settings ADD CONSTRAINT staff_settings_store_id_name_key UNIQUE (store_id, name);
            END IF;
        END$$
    """)

    cur.execute("""
        CREATE TABLE IF NOT EXISTS schedules (
            id               SERIAL PRIMARY KEY,
            schedule_time    TIME NOT NULL DEFAULT '21:00',
            active_receiver  TEXT,
            line_notify_id   TEXT,
            last_sent_date   DATE,
            updated_at       TIMESTAMPTZ DEFAULT NOW()
        )
    """)

    cur.execute("SELECT COUNT(*) AS n FROM products")
    if cur.fetchone()["n"] == 0:
        cur.execute("INSERT INTO products (name,unit,unit_price,store_id) VALUES ('鮮奶','瓶',35,%s) ON CONFLICT DO NOTHING", (default_store_id,))

    cur.execute("SELECT COUNT(*) AS n FROM staff_settings")
    if cur.fetchone()["n"] == 0:
        for name in ["林世杰","張店長","陳組長"]:
            cur.execute("INSERT INTO staff_settings (name,store_id) VALUES (%s,%s) ON CONFLICT DO NOTHING",(name, default_store_id))

    cur.execute("SELECT COUNT(*) AS n FROM schedules")
    if cur.fetchone()["n"] == 0:
        cur.execute("INSERT INTO schedules (schedule_time) VALUES ('21:00')")

    cur.execute("""
        CREATE TABLE IF NOT EXISTS line_groups (
            id             SERIAL PRIMARY KEY,
            group_id       TEXT UNIQUE NOT NULL,
            group_name     TEXT,
            schedule_time  TIME NOT NULL DEFAULT '21:00',
            active         BOOLEAN NOT NULL DEFAULT TRUE,
            last_sent_date DATE,
            weekly_time    TIME NOT NULL DEFAULT '09:00',
            weekly_active  BOOLEAN NOT NULL DEFAULT TRUE,
            last_weekly_date DATE,
            joined_at      TIMESTAMPTZ DEFAULT NOW(),
            updated_at     TIMESTAMPTZ DEFAULT NOW()
        )
    """)
    cur.execute("ALTER TABLE line_groups ADD COLUMN IF NOT EXISTS store_id INTEGER REFERENCES stores(id) ON DELETE SET NULL")
    cur.execute("UPDATE line_groups SET store_id=%s WHERE store_id IS NULL", (default_store_id,))

    cur.execute("""
        DO $$ BEGIN
            IF NOT EXISTS (
                SELECT 1 FROM inventory_records WHERE product_id = 55
            ) THEN
                DELETE FROM purchase_orders WHERE product_id = 55;
                DELETE FROM products WHERE id = 55 AND name = '泉洋栗香地瓜';
            END IF;
        END $$
    """)

    cur.execute("""
        DELETE FROM inventory_batches
        WHERE record_id IN (
            SELECT id FROM inventory_records WHERE product_id IS NULL
        )
    """)
    cur.execute("DELETE FROM inventory_records WHERE product_id IS NULL")

    cur.execute("ALTER TABLE schedules ADD COLUMN IF NOT EXISTS line_notify_id TEXT")
    cur.execute("ALTER TABLE line_groups ADD COLUMN IF NOT EXISTS weekly_time    TIME    NOT NULL DEFAULT '09:00'")
    cur.execute("ALTER TABLE line_groups ADD COLUMN IF NOT EXISTS weekly_active  BOOLEAN NOT NULL DEFAULT TRUE")
    cur.execute("ALTER TABLE line_groups ADD COLUMN IF NOT EXISTS last_weekly_date DATE")

    cur.execute("""
        CREATE TABLE IF NOT EXISTS consumption_records (
            id            SERIAL PRIMARY KEY,
            product_id    INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
            recorded_date DATE    NOT NULL,
            quantity      INTEGER NOT NULL DEFAULT 0,
            staff_name    TEXT,
            note          TEXT,
            created_at    TIMESTAMPTZ DEFAULT NOW(),
            UNIQUE (product_id, recorded_date)
        )
    """)
    cur.execute("ALTER TABLE consumption_records ADD COLUMN IF NOT EXISTS store_id INTEGER REFERENCES stores(id) ON DELETE SET NULL")
    cur.execute("UPDATE consumption_records SET store_id=%s WHERE store_id IS NULL", (default_store_id,))

    cur.execute("""
        CREATE TABLE IF NOT EXISTS purchase_orders (
            id              SERIAL PRIMARY KEY,
            product_id      INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
            product_name    TEXT NOT NULL,
            unit            TEXT NOT NULL,
            quantity        INTEGER NOT NULL,
            ordered_by      TEXT NOT NULL,
            ordered_date    DATE NOT NULL,
            arrived         BOOLEAN NOT NULL DEFAULT FALSE,
            arrived_date    DATE,
            arrived_by      TEXT,
            arrived_quantity INTEGER,
            note            TEXT,
            created_at      TIMESTAMPTZ DEFAULT NOW()
        )
    """)
    cur.execute("ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS arrived_quantity INTEGER")
    cur.execute("ALTER TABLE purchase_orders ADD COLUMN IF NOT EXISTS store_id INTEGER REFERENCES stores(id) ON DELETE SET NULL")
    cur.execute("UPDATE purchase_orders SET store_id=%s WHERE store_id IS NULL", (default_store_id,))

    # ── product_categories ─────────────────────────────────────
    cur.execute("""
        CREATE TABLE IF NOT EXISTS product_categories (
            id         SERIAL PRIMARY KEY,
            name       TEXT NOT NULL,
            created_at TIMESTAMPTZ DEFAULT NOW()
        )
    """)
    cur.execute("ALTER TABLE product_categories ADD COLUMN IF NOT EXISTS store_id INTEGER REFERENCES stores(id) ON DELETE SET NULL")
    cur.execute("UPDATE product_categories SET store_id=%s WHERE store_id IS NULL", (default_store_id,))
    # ★ Phase 2：分類唯一性改成「同店內不可重複」
    cur.execute("""
        DO $$
        BEGIN
            IF EXISTS (
                SELECT 1 FROM pg_constraint
                WHERE conname = 'product_categories_name_key' AND conrelid = 'product_categories'::regclass
            ) THEN
                ALTER TABLE product_categories DROP CONSTRAINT product_categories_name_key;
            END IF;
            IF NOT EXISTS (
                SELECT 1 FROM pg_constraint
                WHERE conname = 'product_categories_store_id_name_key' AND conrelid = 'product_categories'::regclass
            ) THEN
                ALTER TABLE product_categories ADD CONSTRAINT product_categories_store_id_name_key UNIQUE (store_id, name);
            END IF;
        END$$
    """)
    for cat in ['奶製品','咖啡豆及茶葉','麵包','冷凍食材','冷藏食材','乾貨']:
        cur.execute(
            "INSERT INTO product_categories (name, store_id) VALUES (%s,%s) ON CONFLICT DO NOTHING",
            (cat, default_store_id)
        )
    cur.execute("""
        INSERT INTO product_categories (name, store_id)
        SELECT DISTINCT category, %s FROM products
        WHERE category IS NOT NULL AND category != ''
        ON CONFLICT DO NOTHING
    """, (default_store_id,))

    conn.commit(); cur.close(); conn.close()

@app.on_event("startup")
def on_startup(): init_db()

# ── Pydantic ──────────────────────────────────────────────────
class LoginRequest(BaseModel):
    username: str
    password: str

class ChangePasswordRequest(BaseModel):
    old_password: str
    new_password: str

class UserCreateRequest(BaseModel):
    username: str
    password: str
    role: str = "staff"
    store_id: Optional[int] = None

class UserUpdateRequest(BaseModel):
    role:     Optional[str] = None
    store_id: Optional[int] = None

class StoreCreateRequest(BaseModel):
    name: str
    code: str

class StoreUpdateRequest(BaseModel):
    name:   Optional[str]  = None
    code:   Optional[str]  = None
    active: Optional[bool] = None

class ProductCreateRequest(BaseModel):
    name:               str
    unit:               str   = "瓶"
    unit_price:         float = 0
    default_shelf_days: Optional[int] = None
    min_threshold:      int   = 0
    category:           Optional[str] = None

class ProductUpdateRequest(BaseModel):
    name:               Optional[str]   = None
    unit:               Optional[str]   = None
    unit_price:         Optional[float] = None
    active:             Optional[bool]  = None
    default_shelf_days: Optional[int]  = None
    min_threshold:      Optional[int]  = None
    category:           Optional[str]  = None

class BatchItem(BaseModel):
    expiry_date: str
    quantity:    int

class InventorySaveRequest(BaseModel):
    product_id:    int
    recorded_date: str
    min_threshold: int
    order_date:    str
    staff_name:    str
    batches:       list[BatchItem]

class ConsumptionSaveRequest(BaseModel):
    product_id:    int
    recorded_date: str
    quantity:      int
    staff_name:    Optional[str] = None
    note:          Optional[str] = None

class PurchaseOrderRequest(BaseModel):
    product_id:   int
    quantity:     int
    ordered_by:   str
    ordered_date: str
    note:         Optional[str] = None

class LineGroupUpdateRequest(BaseModel):
    schedule_time:  Optional[str]  = None
    active:         Optional[bool] = None
    weekly_time:    Optional[str]  = None
    weekly_active:  Optional[bool] = None

class StaffAddRequest(BaseModel):
    name: str

# ── AUTH ──────────────────────────────────────────────────────
@app.post("/auth/login")
def login(body: LoginRequest):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("""
        SELECT u.username, u.password_hash, u.role, u.store_id, s.name AS store_name
        FROM users u
        LEFT JOIN stores s ON s.id = u.store_id
        WHERE u.username=%s
    """, (body.username,))
    user = cur.fetchone(); cur.close(); conn.close()
    if not user or not verify_password(body.password, user["password_hash"]):
        raise HTTPException(status_code=401, detail="帳號或密碼錯誤")
    return {
        "token": create_token(user["username"], user["role"], user["store_id"]),
        "username": user["username"], "role": user["role"],
        "store_id": user["store_id"], "store_name": user["store_name"],
    }

@app.get("/users/me")
def get_me(td: dict = Depends(decode_token)):
    store_name = None
    if td["store_id"]:
        conn = get_conn(); cur = conn.cursor()
        cur.execute("SELECT name FROM stores WHERE id=%s", (td["store_id"],))
        row = cur.fetchone(); cur.close(); conn.close()
        store_name = row["name"] if row else None
    return {"username": td["username"], "role": td["role"],
            "store_id": td["store_id"], "store_name": store_name}

@app.put("/users/me/password")
def change_password(body: ChangePasswordRequest, td: dict = Depends(decode_token)):
    if len(body.new_password) < 6:
        raise HTTPException(status_code=400, detail="新密碼至少 6 個字元")
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT password_hash FROM users WHERE username=%s",(td["username"],))
    user = cur.fetchone()
    if not user or not verify_password(body.old_password, user["password_hash"]):
        cur.close(); conn.close()
        raise HTTPException(status_code=401, detail="舊密碼輸入錯誤")
    cur.execute("UPDATE users SET password_hash=%s WHERE username=%s",
                (hash_password(body.new_password), td["username"]))
    conn.commit(); cur.close(); conn.close()
    return {"ok": True}

# ── STORES ────────────────────────────────────────────────────
@app.get("/stores")
def list_stores(td: dict = Depends(decode_token)):
    conn = get_conn(); cur = conn.cursor()
    if td["role"] == "superadmin":
        cur.execute("SELECT id, name, code, active, created_at::text FROM stores ORDER BY id")
    else:
        cur.execute("SELECT id, name, code, active, created_at::text FROM stores WHERE id=%s", (td["store_id"],))
    rows = [dict(r) for r in cur.fetchall()]
    cur.close(); conn.close()
    return {"stores": rows}

@app.post("/stores", status_code=201)
def create_store(body: StoreCreateRequest, _: dict = Depends(require_superadmin)):
    name = body.name.strip()
    code = body.code.strip().upper()
    if not name or not code:
        raise HTTPException(status_code=400, detail="店名與店代碼皆不可為空")
    conn = get_conn(); cur = conn.cursor()
    try:
        cur.execute("INSERT INTO stores (name, code) VALUES (%s,%s) RETURNING id", (name, code))
        new_id = cur.fetchone()["id"]; conn.commit()
    except psycopg2.errors.UniqueViolation:
        conn.rollback(); raise HTTPException(status_code=409, detail="此店代碼已存在")
    finally:
        cur.close(); conn.close()
    return {"ok": True, "id": new_id, "name": name, "code": code}

@app.put("/stores/{store_id}")
def update_store(store_id: int, body: StoreUpdateRequest, _: dict = Depends(require_superadmin)):
    conn = get_conn(); cur = conn.cursor()
    try:
        if body.name   is not None: cur.execute("UPDATE stores SET name=%s   WHERE id=%s", (body.name.strip(), store_id))
        if body.code   is not None: cur.execute("UPDATE stores SET code=%s   WHERE id=%s", (body.code.strip().upper(), store_id))
        if body.active is not None: cur.execute("UPDATE stores SET active=%s WHERE id=%s", (body.active, store_id))
        conn.commit()
    except psycopg2.errors.UniqueViolation:
        conn.rollback(); raise HTTPException(status_code=409, detail="此店代碼已存在")
    finally:
        cur.close(); conn.close()
    return {"ok": True}

@app.delete("/stores/{store_id}")
def delete_store(store_id: int, _: dict = Depends(require_superadmin)):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT COUNT(*) AS n FROM users WHERE store_id=%s", (store_id,))
    if cur.fetchone()["n"] > 0:
        cur.close(); conn.close()
        raise HTTPException(status_code=409, detail="此店還有帳號使用中，請先移除或轉移帳號")
    cur.execute("SELECT COUNT(*) AS n FROM products WHERE store_id=%s", (store_id,))
    if cur.fetchone()["n"] > 0:
        cur.close(); conn.close()
        raise HTTPException(status_code=409, detail="此店還有品項資料，無法刪除")
    cur.execute("DELETE FROM stores WHERE id=%s RETURNING id", (store_id,))
    deleted = cur.fetchone(); conn.commit(); cur.close(); conn.close()
    if not deleted:
        raise HTTPException(status_code=404, detail="找不到此店家")
    return {"ok": True}

# ── USERS ─────────────────────────────────────────────────────
@app.get("/users")
def list_users(td: dict = Depends(require_admin)):
    conn = get_conn(); cur = conn.cursor()
    if td["role"] == "superadmin":
        cur.execute("""
            SELECT u.username, u.role, u.store_id, s.name AS store_name, u.created_at::text
            FROM users u LEFT JOIN stores s ON s.id=u.store_id
            ORDER BY u.id
        """)
    else:
        cur.execute("""
            SELECT u.username, u.role, u.store_id, s.name AS store_name, u.created_at::text
            FROM users u LEFT JOIN stores s ON s.id=u.store_id
            WHERE u.store_id=%s
            ORDER BY u.id
        """, (td["store_id"],))
    rows = [dict(r) for r in cur.fetchall()]
    cur.close(); conn.close()
    return {"users": rows}

@app.post("/users", status_code=201)
def create_user(body: UserCreateRequest, td: dict = Depends(require_admin)):
    if body.role not in ("superadmin", "admin", "staff"):
        raise HTTPException(status_code=400, detail="role 只允許 superadmin、admin 或 staff")
    if len(body.password) < 6:
        raise HTTPException(status_code=400, detail="密碼至少 6 個字元")

    if td["role"] == "superadmin":
        if body.role == "superadmin":
            store_id = None
        else:
            if not body.store_id:
                raise HTTPException(status_code=400, detail="請指定所屬店家")
            store_id = body.store_id
    else:
        if body.role == "superadmin":
            raise HTTPException(status_code=403, detail="店長無法建立總管理員帳號")
        store_id = td["store_id"]

    conn = get_conn(); cur = conn.cursor()
    try:
        cur.execute("INSERT INTO users (username,password_hash,role,store_id) VALUES (%s,%s,%s,%s)",
                    (body.username.strip(), hash_password(body.password), body.role, store_id))
        conn.commit()
    except psycopg2.errors.UniqueViolation:
        conn.rollback(); raise HTTPException(status_code=409, detail="此帳號已存在")
    finally:
        cur.close(); conn.close()
    return {"ok": True, "username": body.username, "role": body.role, "store_id": store_id}

@app.put("/users/{username}")
def update_user(username: str, body: UserUpdateRequest, td: dict = Depends(require_admin)):
    """調整帳號角色 / 所屬店家。可用來修正誤升級的帳號，或日常管理用。"""
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT role, store_id FROM users WHERE username=%s", (username,))
    target = cur.fetchone()
    if not target:
        cur.close(); conn.close()
        raise HTTPException(status_code=404, detail="找不到此帳號")

    if td["role"] != "superadmin":
        if target["store_id"] != td["store_id"]:
            cur.close(); conn.close()
            raise HTTPException(status_code=403, detail="只能修改自己店內的帳號")
        if body.role == "superadmin":
            cur.close(); conn.close()
            raise HTTPException(status_code=403, detail="店長無法設定總管理員角色")
        if body.store_id is not None and body.store_id != td["store_id"]:
            cur.close(); conn.close()
            raise HTTPException(status_code=403, detail="店長無法把帳號調到別的店")

    new_role = body.role if body.role is not None else target["role"]
    if new_role not in ("superadmin", "admin", "staff"):
        cur.close(); conn.close()
        raise HTTPException(status_code=400, detail="role 只允許 superadmin、admin 或 staff")

    if new_role == "superadmin":
        new_store_id = None
    else:
        new_store_id = body.store_id if body.store_id is not None else target["store_id"]
        if not new_store_id:
            cur.close(); conn.close()
            raise HTTPException(status_code=400, detail="admin/staff 必須指定所屬店家")

    if target["role"] == "superadmin" and new_role != "superadmin":
        cur.execute("SELECT COUNT(*) AS n FROM users WHERE role='superadmin' AND username!=%s", (username,))
        if cur.fetchone()["n"] == 0:
            cur.close(); conn.close()
            raise HTTPException(status_code=400, detail="系統中必須保留至少一個總管理員帳號")

    cur.execute("UPDATE users SET role=%s, store_id=%s WHERE username=%s", (new_role, new_store_id, username))
    conn.commit(); cur.close(); conn.close()
    return {"ok": True, "username": username, "role": new_role, "store_id": new_store_id}

@app.delete("/users/{username}")
def delete_user(username: str, td: dict = Depends(require_admin)):
    admin_username = td["username"]
    if username == admin_username:
        raise HTTPException(status_code=400, detail="不能刪除自己的帳號")

    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT role, store_id FROM users WHERE username=%s", (username,))
    target = cur.fetchone()
    if not target:
        cur.close(); conn.close()
        raise HTTPException(status_code=404, detail="找不到此帳號")

    if td["role"] != "superadmin" and target["store_id"] != td["store_id"]:
        cur.close(); conn.close()
        raise HTTPException(status_code=403, detail="只能刪除自己店內的帳號")

    if target["role"] == "superadmin":
        cur.execute("SELECT COUNT(*) AS n FROM users WHERE role='superadmin' AND username!=%s",(username,))
        if cur.fetchone()["n"] == 0:
            cur.close(); conn.close()
            raise HTTPException(status_code=400, detail="系統中必須保留至少一個總管理員帳號")

    cur.execute("DELETE FROM users WHERE username=%s RETURNING id",(username,))
    deleted = cur.fetchone(); conn.commit(); cur.close(); conn.close()
    if not deleted: raise HTTPException(status_code=404, detail="找不到此帳號")
    return {"ok": True}

# ── CATEGORIES（★ Phase 2：依店過濾）─────────────────────────
@app.get("/categories")
def list_categories(store: dict = Depends(get_active_store)):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT id, name, created_at::text FROM product_categories WHERE store_id=%s ORDER BY name",
                (store["store_id"],))
    rows = [dict(r) for r in cur.fetchall()]
    cur.close(); conn.close()
    return {"categories": rows}

@app.post("/categories", status_code=201)
def create_category(body: dict, store: dict = Depends(require_store_admin)):
    name = (body.get("name") or "").strip()
    if not name:
        raise HTTPException(status_code=400, detail="分類名稱不可為空")
    conn = get_conn(); cur = conn.cursor()
    try:
        cur.execute("INSERT INTO product_categories (name, store_id) VALUES (%s,%s) RETURNING id",
                    (name, store["store_id"]))
        new_id = cur.fetchone()["id"]
        conn.commit()
    except psycopg2.errors.UniqueViolation:
        conn.rollback()
        raise HTTPException(status_code=409, detail="此分類已存在")
    finally:
        cur.close(); conn.close()
    return {"ok": True, "id": new_id, "name": name}

@app.delete("/categories/{name}")
def delete_category(name: str, store: dict = Depends(require_store_admin)):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT COUNT(*) AS n FROM products WHERE category=%s AND active=TRUE AND store_id=%s",
                (name, store["store_id"]))
    if cur.fetchone()["n"] > 0:
        cur.close(); conn.close()
        raise HTTPException(status_code=409, detail=f"還有品項使用「{name}」分類，請先修改品項再刪除")
    cur.execute("DELETE FROM product_categories WHERE name=%s AND store_id=%s RETURNING id",
                (name, store["store_id"]))
    deleted = cur.fetchone(); conn.commit(); cur.close(); conn.close()
    if not deleted:
        raise HTTPException(status_code=404, detail="找不到此分類")
    return {"ok": True}

# ── PRODUCTS（★ Phase 2：依店過濾）───────────────────────────
@app.get("/products")
def list_products(store: dict = Depends(get_active_store)):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("""
        SELECT id,name,unit,unit_price::float,active,default_shelf_days,min_threshold,category
        FROM products WHERE store_id=%s ORDER BY id
    """, (store["store_id"],))
    rows = [dict(r) for r in cur.fetchall()]
    cur.close(); conn.close()
    return {"products": rows}

@app.post("/products", status_code=201)
def create_product(body: ProductCreateRequest, store: dict = Depends(require_store_admin)):
    store_id = store["store_id"]
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT id, active FROM products WHERE name=%s AND store_id=%s", (body.name.strip(), store_id))
    existing = cur.fetchone()
    if existing:
        cur.close(); conn.close()
        if existing["active"]:
            raise HTTPException(status_code=409, detail=f"品項「{body.name.strip()}」已存在，請確認品項清單")
        else:
            raise HTTPException(status_code=409, detail=f"品項「{body.name.strip()}」已存在但已停用，請在品項設定重新啟用，而非新增")
    try:
        cur.execute("""
            INSERT INTO products (name,unit,unit_price,min_threshold,default_shelf_days,category,store_id)
            VALUES (%s,%s,%s,%s,%s,%s,%s) RETURNING id
        """, (body.name.strip(), body.unit.strip(), body.unit_price,
              body.min_threshold, body.default_shelf_days, body.category, store_id))
        new_id = cur.fetchone()["id"]; conn.commit()
    except psycopg2.errors.UniqueViolation:
        conn.rollback()
        raise HTTPException(status_code=409, detail=f"品項「{body.name.strip()}」已存在")
    finally:
        cur.close(); conn.close()
    return {"ok": True, "id": new_id}

@app.put("/products/{product_id}")
def update_product(product_id: int, body: ProductUpdateRequest, store: dict = Depends(require_store_admin)):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT id FROM products WHERE id=%s AND store_id=%s", (product_id, store["store_id"]))
    if not cur.fetchone():
        cur.close(); conn.close()
        raise HTTPException(status_code=404, detail="找不到此品項")
    try:
        if body.name               is not None: cur.execute("UPDATE products SET name=%s               WHERE id=%s",(body.name.strip(), product_id))
        if body.unit               is not None: cur.execute("UPDATE products SET unit=%s               WHERE id=%s",(body.unit,        product_id))
        if body.unit_price         is not None: cur.execute("UPDATE products SET unit_price=%s         WHERE id=%s",(body.unit_price,  product_id))
        if body.active             is not None: cur.execute("UPDATE products SET active=%s             WHERE id=%s",(body.active,      product_id))
        if body.default_shelf_days is not None: cur.execute("UPDATE products SET default_shelf_days=%s WHERE id=%s",(body.default_shelf_days, product_id))
        if body.min_threshold      is not None: cur.execute("UPDATE products SET min_threshold=%s      WHERE id=%s",(body.min_threshold,      product_id))
        if body.category           is not None: cur.execute("UPDATE products SET category=%s           WHERE id=%s",(body.category,           product_id))
        conn.commit()
    except psycopg2.errors.UniqueViolation:
        conn.rollback()
        raise HTTPException(status_code=409, detail="此品項名稱已存在")
    finally:
        cur.close(); conn.close()
    return {"ok": True}

@app.delete("/products/{product_id}")
def delete_product(product_id: int, store: dict = Depends(require_store_admin)):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("DELETE FROM products WHERE id=%s AND store_id=%s RETURNING id",(product_id, store["store_id"]))
    deleted = cur.fetchone(); conn.commit(); cur.close(); conn.close()
    if not deleted: raise HTTPException(status_code=404, detail="找不到此品項")
    return {"ok": True}

# ── INVENTORY（★ Phase 2：依店過濾）─────────────────────────
@app.get("/inventory/latest")
def get_latest_inventory(store: dict = Depends(get_active_store)):
    store_id = store["store_id"]
    conn = get_conn(); cur = conn.cursor()

    cur.execute("""
        SELECT id, name, unit, unit_price::float, default_shelf_days, min_threshold, category
        FROM products WHERE active=TRUE AND store_id=%s ORDER BY id
    """, (store_id,))
    all_products = cur.fetchall()

    if not all_products:
        cur.close(); conn.close()
        return {"recorded_date": None, "items": []}

    cur.execute("""
        SELECT DISTINCT ON (p.id)
               p.id AS product_id,
               r.id AS record_id,
               r.recorded_date::text,
               r.min_threshold,
               r.order_date::text,
               r.staff_name
        FROM products p
        LEFT JOIN inventory_records r ON r.product_id = p.id AND r.store_id = %s
        WHERE p.active = TRUE AND p.store_id = %s
        ORDER BY p.id, r.recorded_date DESC NULLS LAST
    """, (store_id, store_id))
    record_map = {row["product_id"]: row for row in cur.fetchall()}

    from collections import Counter
    dates = [r["recorded_date"] for r in record_map.values() if r.get("recorded_date")]
    latest = Counter(dates).most_common(1)[0][0] if dates else None

    items = []
    for prod in all_products:
        pid = prod["id"]
        rec = record_map.get(pid, {})
        record_id = rec.get("record_id")

        batches = []
        if record_id:
            cur.execute("""
                SELECT expiry_date::text, quantity
                FROM inventory_batches WHERE record_id=%s ORDER BY expiry_date
            """, (record_id,))
            batches = [dict(b) for b in cur.fetchall()]

        total_qty = sum(b["quantity"] for b in batches)
        items.append({
            "product_id":        pid,
            "product_name":      prod["name"],
            "unit":              prod["unit"],
            "unit_price":        prod["unit_price"],
            "category":          prod["category"],
            "default_shelf_days":prod["default_shelf_days"],
            "min_threshold":     rec.get("min_threshold") or prod["min_threshold"] or 0,
            "order_date":        rec.get("order_date"),
            "staff_name":        rec.get("staff_name"),
            "recorded_date":     rec.get("recorded_date"),
            "total_qty":         total_qty,
            "batches":           batches,
        })

    cur.close(); conn.close()
    return {"recorded_date": latest, "items": items}

@app.post("/inventory/save", status_code=201)
def save_inventory(body: InventorySaveRequest, background_tasks: BackgroundTasks, store: dict = Depends(get_active_store)):
    store_id = store["store_id"]
    if not body.batches:
        raise HTTPException(status_code=400, detail="至少需要一筆效期批次")
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT id FROM products WHERE id=%s AND store_id=%s", (body.product_id, store_id))
    if not cur.fetchone():
        cur.close(); conn.close()
        raise HTTPException(status_code=404, detail="找不到此品項")
    try:
        cur.execute("""
            INSERT INTO inventory_records (product_id,recorded_date,min_threshold,order_date,staff_name,store_id,updated_at)
            VALUES (%s,%s,%s,%s,%s,%s,NOW())
            ON CONFLICT (product_id, recorded_date)
            DO UPDATE SET min_threshold=%s, order_date=%s, staff_name=%s, updated_at=NOW()
            RETURNING id
        """, (body.product_id, body.recorded_date,
              body.min_threshold, body.order_date, body.staff_name, store_id,
              body.min_threshold, body.order_date, body.staff_name))
        record_id = cur.fetchone()["id"]

        cur.execute("DELETE FROM inventory_batches WHERE record_id=%s", (record_id,))
        psycopg2.extras.execute_batch(cur,
            "INSERT INTO inventory_batches (record_id,expiry_date,quantity) VALUES (%s,%s,%s)",
            [(record_id, b.expiry_date, b.quantity) for b in body.batches]
        )

        cur.execute("UPDATE products SET min_threshold=%s WHERE id=%s",
                    (body.min_threshold, body.product_id))
        conn.commit()

        TW    = timezone(timedelta(hours=8))
        today = datetime.now(TW).date().isoformat()
        cur.execute("""
            SELECT id FROM purchase_orders
            WHERE product_id=%s AND arrived=FALSE AND store_id=%s
            ORDER BY ordered_date ASC
        """, (body.product_id, store_id))
        pending = cur.fetchall()
        auto_arrived = False
        if len(pending) == 1:
            cur.execute("""
                UPDATE purchase_orders SET arrived=TRUE, arrived_date=%s, arrived_by=%s
                WHERE id=%s
            """, (today, body.staff_name or "", pending[0]["id"]))
            conn.commit()
            auto_arrived = True

        total_qty = sum(b.quantity for b in body.batches)
        if body.min_threshold > 0 and total_qty < body.min_threshold:
            background_tasks.add_task(_reset_daily_sent_for_alert, store_id)

        cur.close(); conn.close()
        return {"ok": True, "record_id": record_id, "auto_arrived": auto_arrived}

    except Exception as e:
        conn.rollback(); cur.close(); conn.close()
        import logging; logging.getLogger("stockflow").error("save_inventory 失敗：%s", e)
        raise HTTPException(status_code=500, detail=f"儲存失敗：{str(e)}")

def _reset_daily_sent_for_alert(store_id: int):
    try:
        conn = get_conn(); cur = conn.cursor()
        cur.execute("UPDATE line_groups SET last_sent_date = NULL WHERE active = TRUE AND store_id = %s", (store_id,))
        conn.commit(); cur.close(); conn.close()
        import logging
        logging.getLogger("stockflow").info("庫存不足警示：已重置 store %s 的 last_sent_date，等待 scheduler 重新推播", store_id)
    except Exception as e:
        import logging
        logging.getLogger("stockflow").error("重置 last_sent_date 失敗：%s", e)

# ── CONSUMPTION（★ Phase 2：依店過濾）───────────────────────
@app.get("/inventory/active-batches")
def get_active_batches(store: dict = Depends(get_active_store)):
    store_id = store["store_id"]
    conn = get_conn(); cur = conn.cursor()
    today = datetime.now(timezone(timedelta(hours=8))).date()

    cur.execute("""
        SELECT DISTINCT ON (r.product_id)
               r.id AS record_id, r.product_id,
               p.name AS product_name, p.unit
        FROM inventory_records r
        JOIN products p ON p.id = r.product_id
        WHERE p.active = TRUE AND p.store_id = %s
        ORDER BY r.product_id, r.recorded_date DESC
    """, (store_id,))
    records = cur.fetchall()

    result = []
    for rec in records:
        cur.execute("""
            SELECT expiry_date::text, quantity
            FROM inventory_batches WHERE record_id = %s
            ORDER BY expiry_date
        """, (rec["record_id"],))
        batches = cur.fetchall()
        active_batches  = [b for b in batches if b["expiry_date"] >= today.isoformat()]
        expired_batches = [b for b in batches if b["expiry_date"] <  today.isoformat()]
        result.append({
            "product_id":      rec["product_id"],
            "product_name":    rec["product_name"],
            "unit":            rec["unit"],
            "active_batches":  [dict(b) for b in active_batches],
            "expired_qty":     sum(b["quantity"] for b in expired_batches),
        })
    cur.close(); conn.close()
    return {"items": result}

@app.post("/consumption/save", status_code=201)
def save_consumption(body: ConsumptionSaveRequest, store: dict = Depends(get_active_store)):
    store_id = store["store_id"]
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT id FROM products WHERE id=%s AND store_id=%s", (body.product_id, store_id))
    if not cur.fetchone():
        cur.close(); conn.close()
        raise HTTPException(status_code=404, detail="找不到此品項")
    cur.execute("""
        INSERT INTO consumption_records (product_id, recorded_date, quantity, staff_name, note, store_id)
        VALUES (%s, %s, %s, %s, %s, %s)
        ON CONFLICT (product_id, recorded_date)
        DO UPDATE SET quantity=%s, staff_name=%s, note=%s, created_at=NOW()
    """, (
        body.product_id, body.recorded_date, body.quantity, body.staff_name, body.note, store_id,
        body.quantity, body.staff_name, body.note,
    ))
    conn.commit(); cur.close(); conn.close()
    return {"ok": True}

@app.get("/consumption/stats")
def get_consumption_stats(store: dict = Depends(get_active_store)):
    store_id = store["store_id"]
    conn = get_conn(); cur = conn.cursor()

    TW    = timezone(timedelta(hours=8))
    today = datetime.now(TW).date()
    weekday      = today.weekday()
    week_start   = today - timedelta(days=weekday)
    week_end     = today
    lw_start     = week_start - timedelta(days=7)
    lw_end       = week_start - timedelta(days=1)
    month_start  = today.replace(day=1)
    lm_start     = (month_start - timedelta(days=1)).replace(day=1)
    lm_end       = month_start - timedelta(days=1)

    cur.execute("""
        SELECT
            p.id          AS product_id,
            p.name        AS product_name,
            p.unit,
            p.unit_price::float,
            COALESCE(SUM(c.quantity) FILTER (
                WHERE c.recorded_date BETWEEN %s AND %s
            ), 0) AS this_week,
            COALESCE(SUM(c.quantity) FILTER (
                WHERE c.recorded_date BETWEEN %s AND %s
            ), 0) AS last_week,
            COALESCE(SUM(c.quantity) FILTER (
                WHERE c.recorded_date BETWEEN %s AND %s
            ), 0) AS this_month,
            COALESCE(SUM(c.quantity) FILTER (
                WHERE c.recorded_date BETWEEN %s AND %s
            ), 0) AS last_month
        FROM products p
        LEFT JOIN consumption_records c ON c.product_id = p.id
        WHERE p.active = TRUE AND p.store_id = %s
        GROUP BY p.id, p.name, p.unit, p.unit_price
        ORDER BY p.id
    """, (
        week_start, week_end,
        lw_start,   lw_end,
        month_start, today,
        lm_start,   lm_end,
        store_id,
    ))
    stats = cur.fetchall()

    cur.execute("""
        SELECT DISTINCT ON (r.product_id)
               r.product_id,
               r.min_threshold,
               COALESCE((
                   SELECT SUM(b.quantity)
                   FROM inventory_batches b
                   WHERE b.record_id = r.id
               ), 0) AS current_qty
        FROM inventory_records r
        WHERE r.store_id = %s
        ORDER BY r.product_id, r.recorded_date DESC
    """, (store_id,))
    latest_inv = {row["product_id"]: row for row in cur.fetchall()}

    cur.close(); conn.close()

    result = []
    for s in stats:
        inv      = latest_inv.get(s["product_id"])
        cur_qty  = int(inv["current_qty"])  if inv else 0
        thresh   = int(inv["min_threshold"]) if inv else 0
        suggest  = max(0, thresh - cur_qty) if thresh > 0 else 0
        result.append({
            "product_id":   s["product_id"],
            "product_name": s["product_name"],
            "unit":         s["unit"],
            "unit_price":   s["unit_price"],
            "this_week":    int(s["this_week"]),
            "last_week":    int(s["last_week"]),
            "this_month":   int(s["this_month"]),
            "last_month":   int(s["last_month"]),
            "current_qty":  cur_qty,
            "min_threshold":thresh,
            "suggest_order":suggest,
        })

    return {"stats": result}

# ── PURCHASE ORDERS（★ Phase 2：依店過濾）───────────────────
@app.post("/orders", status_code=201)
def create_order(body: PurchaseOrderRequest, store: dict = Depends(get_active_store)):
    store_id = store["store_id"]
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT name, unit FROM products WHERE id=%s AND store_id=%s", (body.product_id, store_id))
    prod = cur.fetchone()
    if not prod:
        cur.close(); conn.close()
        raise HTTPException(status_code=404, detail="找不到此品項")
    cur.execute("""
        INSERT INTO purchase_orders
          (product_id, product_name, unit, quantity, ordered_by, ordered_date, note, store_id)
        VALUES (%s,%s,%s,%s,%s,%s,%s,%s) RETURNING id
    """, (body.product_id, prod["name"], prod["unit"],
          body.quantity, body.ordered_by, body.ordered_date, body.note, store_id))
    new_id = cur.fetchone()["id"]
    conn.commit(); cur.close(); conn.close()
    return {"ok": True, "id": new_id}

@app.get("/orders")
def list_orders(store: dict = Depends(get_active_store)):
    store_id = store["store_id"]
    TW     = timezone(timedelta(hours=8))
    today  = datetime.now(TW).date()
    cutoff = (today - timedelta(days=3)).isoformat()
    conn = get_conn(); cur = conn.cursor()
    cur.execute("""
        SELECT id, product_id, product_name, unit, quantity,
               ordered_by, ordered_date::text,
               arrived, arrived_date::text, arrived_by, arrived_quantity, note,
               created_at::text
        FROM purchase_orders
        WHERE store_id=%s AND (arrived = FALSE OR arrived_date >= %s)
        ORDER BY arrived ASC, ordered_date DESC, id DESC
    """, (store_id, cutoff))
    rows = [dict(r) for r in cur.fetchall()]
    cur.close(); conn.close()
    return {"orders": rows}

@app.put("/orders/{order_id}/arrive")
def mark_arrived(order_id: int, body: dict = {}, store: dict = Depends(get_active_store)):
    store_id = store["store_id"]
    TW    = timezone(timedelta(hours=8))
    today = datetime.now(TW).date().isoformat()
    staff        = body.get("staff_name", "") if isinstance(body, dict) else ""
    arrived_qty  = body.get("arrived_quantity") if isinstance(body, dict) else None
    expiry_date  = body.get("expiry_date") if isinstance(body, dict) else None

    conn = get_conn(); cur = conn.cursor()

    cur.execute("SELECT * FROM purchase_orders WHERE id=%s AND store_id=%s", (order_id, store_id))
    order = cur.fetchone()
    if not order:
        cur.close(); conn.close()
        raise HTTPException(status_code=404, detail="找不到此叫貨記錄")

    if order["arrived"]:
        cur.close(); conn.close()
        raise HTTPException(status_code=409, detail="此叫貨記錄已標記到貨，請勿重複確認")

    final_qty = arrived_qty if arrived_qty is not None else order["quantity"]

    try:
        cur.execute("""
            UPDATE purchase_orders
            SET arrived=TRUE, arrived_date=%s, arrived_by=%s, arrived_quantity=%s
            WHERE id=%s
        """, (today, staff, final_qty, order_id))

        if expiry_date and final_qty:
            pid = order["product_id"]
            cur.execute("""
                SELECT DISTINCT ON (product_id)
                       id, min_threshold, order_date::text, staff_name
                FROM inventory_records
                WHERE product_id=%s AND store_id=%s
                ORDER BY product_id, recorded_date DESC
            """, (pid, store_id))
            latest_rec = cur.fetchone()

            existing_batches = []
            if latest_rec:
                cur.execute("""
                    SELECT expiry_date::text, quantity
                    FROM inventory_batches WHERE record_id=%s
                    ORDER BY expiry_date
                """, (latest_rec["id"],))
                existing_batches = [dict(b) for b in cur.fetchall()]

            ex = next((b for b in existing_batches if b["expiry_date"] == expiry_date), None)
            if ex:
                ex["quantity"] += final_qty
            else:
                existing_batches.append({"expiry_date": expiry_date, "quantity": final_qty})

            thresh = latest_rec["min_threshold"] if latest_rec else 0
            cur.execute("""
                INSERT INTO inventory_records
                  (product_id, recorded_date, min_threshold, order_date, staff_name, store_id, updated_at)
                VALUES (%s,%s,%s,%s,%s,%s,NOW())
                ON CONFLICT (product_id, recorded_date)
                DO UPDATE SET min_threshold=%s, order_date=%s, staff_name=%s, updated_at=NOW()
                RETURNING id
            """, (pid, today, thresh, today, staff or "", store_id,
                  thresh, today, staff or ""))
            new_record_id = cur.fetchone()["id"]

            cur.execute("DELETE FROM inventory_batches WHERE record_id=%s", (new_record_id,))
            for b in existing_batches:
                cur.execute("""
                    INSERT INTO inventory_batches (record_id, expiry_date, quantity)
                    VALUES (%s,%s,%s)
                """, (new_record_id, b["expiry_date"], b["quantity"]))

        conn.commit(); cur.close(); conn.close()
        return {"ok": True, "arrived_quantity": final_qty}

    except Exception as e:
        conn.rollback(); cur.close(); conn.close()
        import logging; logging.getLogger("stockflow").error("mark_arrived 失敗：%s", e)
        raise HTTPException(status_code=500, detail=f"到貨儲存失敗：{str(e)}")

@app.delete("/orders/{order_id}")
def delete_order(order_id: int, store: dict = Depends(require_store_admin)):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("DELETE FROM purchase_orders WHERE id=%s AND store_id=%s RETURNING id", (order_id, store["store_id"]))
    deleted = cur.fetchone(); conn.commit(); cur.close(); conn.close()
    if not deleted:
        raise HTTPException(status_code=404, detail="找不到此叫貨記錄")
    return {"ok": True}

# ── STAFF（★ Phase 2：依店過濾）─────────────────────────────
@app.get("/staff")
def list_staff(store: dict = Depends(get_active_store)):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT name FROM staff_settings WHERE store_id=%s ORDER BY id", (store["store_id"],))
    rows = [dict(r) for r in cur.fetchall()]
    cur.close(); conn.close()
    return {"staff": rows}

@app.post("/staff", status_code=201)
def add_staff(body: StaffAddRequest, store: dict = Depends(require_store_admin)):
    name = body.name.strip()
    if not name: raise HTTPException(status_code=400, detail="姓名不可為空")
    conn = get_conn(); cur = conn.cursor()
    try:
        cur.execute("INSERT INTO staff_settings (name, store_id) VALUES (%s,%s)",(name, store["store_id"])); conn.commit()
    except psycopg2.errors.UniqueViolation:
        conn.rollback(); raise HTTPException(status_code=409, detail="此姓名已存在")
    finally: cur.close(); conn.close()
    return {"ok": True, "name": name}

@app.delete("/staff/{name}")
def delete_staff(name: str, store: dict = Depends(require_store_admin)):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("DELETE FROM staff_settings WHERE name=%s AND store_id=%s RETURNING id",(name, store["store_id"]))
    deleted = cur.fetchone(); conn.commit(); cur.close(); conn.close()
    if not deleted: raise HTTPException(status_code=404, detail="找不到此人員")
    return {"ok": True}

# ── EXPORT（★ Phase 2：依店過濾）────────────────────────────
@app.get("/export/report")
def export_report(store: dict = Depends(require_store_admin)):
    store_id = store["store_id"]
    TW    = timezone(timedelta(hours=8))
    today = datetime.now(TW).date()
    month_start = today.replace(day=1).isoformat()

    conn = get_conn(); cur = conn.cursor()
    cur.execute("""
        SELECT DISTINCT recorded_date FROM inventory_records
        WHERE store_id=%s
        ORDER BY recorded_date DESC LIMIT 1
    """, (store_id,))
    row = cur.fetchone()
    if not row:
        cur.close(); conn.close()
        raise HTTPException(status_code=404, detail="尚無盤點資料")
    latest = row["recorded_date"].isoformat()

    cur.execute("""
        SELECT r.id, r.product_id, p.name AS product_name, p.unit, p.unit_price::float,
               r.min_threshold, r.order_date::text, r.staff_name
        FROM inventory_records r
        JOIN products p ON p.id = r.product_id
        WHERE r.recorded_date = %s AND r.store_id=%s ORDER BY r.product_id
    """, (latest, store_id))
    records = cur.fetchall()

    cur.execute("""
        SELECT r.product_id,
               COALESCE(SUM(b.quantity), 0) AS expired_qty
        FROM inventory_records r
        JOIN inventory_batches b ON b.record_id = r.id
        WHERE r.recorded_date >= %s
          AND b.expiry_date < %s
          AND r.store_id = %s
        GROUP BY r.product_id
    """, (month_start, today.isoformat(), store_id))
    expired_map = {row["product_id"]: int(row["expired_qty"]) for row in cur.fetchall()}

    buf = io.StringIO()
    w   = csv.writer(buf)
    w.writerow(["盤點日","品項名稱","單位","單價","有效期限","數量","小計成本",
                "最低需求","盤點人員","訂貨日","本月過期未用數量"])

    for rec in records:
        cur.execute("""
            SELECT expiry_date::text, quantity FROM inventory_batches
            WHERE record_id=%s ORDER BY expiry_date
        """, (rec["id"],))
        batches = cur.fetchall()
        expired_qty = expired_map.get(rec["product_id"], 0)
        first = True
        for b in batches:
            cost = round(b["quantity"] * rec["unit_price"], 2)
            w.writerow([
                latest, rec["product_name"], rec["unit"], rec["unit_price"],
                b["expiry_date"], b["quantity"], cost,
                rec["min_threshold"], rec["staff_name"], rec["order_date"],
                expired_qty if first else "",
            ])
            first = False

    cur.close(); conn.close()
    buf.seek(0)
    filename = f"inventory_report_{latest}.csv"
    return StreamingResponse(
        iter([buf.getvalue()]),
        media_type="text/csv; charset=utf-8",
        headers={"Content-Disposition": f"attachment; filename={filename}"},
    )

# ── LINE WEBHOOK ──────────────────────────────────────────────
def _get_source_id(source) -> tuple[str, str]:
    if isinstance(source, GroupSource):
        return source.group_id, "group"
    if isinstance(source, RoomSource):
        return source.room_id, "room"
    return source.user_id, "user"

def _line_reply(reply_token: str, text: str, fallback_to: str = None):
    api = get_line_api()
    if not api:
        return
    try:
        api.reply_message(ReplyMessageRequest(
            reply_token=reply_token,
            messages=[TextMessage(text=text)],
        ))
    except Exception:
        if fallback_to:
            try:
                api.push_message(PushMessageRequest(
                    to=fallback_to,
                    messages=[TextMessage(text=text)],
                ))
            except Exception as e:
                import logging
                logging.getLogger("stockflow").error("push fallback 失敗：%s", e)

@app.post("/webhook/line")
async def line_webhook(
    request: Request,
    background_tasks: BackgroundTasks,
    x_line_signature: str = Header(None),
):
    if not _line_parser or not LINE_SECRET:
        raise HTTPException(status_code=503, detail="LINE Bot 未設定")

    body = await request.body()
    body_str = body.decode("utf-8")

    try:
        events = _line_parser.parse(body_str, x_line_signature)
    except InvalidSignatureError:
        raise HTTPException(status_code=400, detail="LINE 簽章驗證失敗")

    background_tasks.add_task(_process_line_events, events)
    return JSONResponse(content={"ok": True})


def _process_line_events(events):
    import logging
    log = logging.getLogger("stockflow")

    for event in events:
        try:
            source_id, source_type = _get_source_id(event.source)

            if isinstance(event, JoinEvent):
                # ★ Phase 2 提醒：這裡先不指定 store_id（等 Phase 3 的「綁定 店代碼」指令才會設定）
                conn = get_conn(); cur = conn.cursor()
                cur.execute("""
                    INSERT INTO line_groups (group_id, group_name, schedule_time, active)
                    VALUES (%s, %s, '21:00', TRUE)
                    ON CONFLICT (group_id) DO UPDATE SET active = TRUE, updated_at = NOW()
                """, (source_id, f"{source_type}:{source_id[:8]}"))
                conn.commit(); cur.close(); conn.close()

                line_api = get_line_api()
                if line_api:
                    try:
                        line_api.push_message(PushMessageRequest(
                            to=source_id,
                            messages=[TextMessage(text=(
                                "📦 StockFlow 庫存通知機器人已加入！\n\n"
                                "這支 Bot 服務多家店，請先綁定要接收哪家店的通知：\n"
                                "・綁定 店代碼 — 例如：綁定 DEFAULT\n"
                                "（店代碼請向店家管理員索取）\n\n"
                                "綁定後預設每天 21:00 推播庫存日報。\n\n"
                                "其他可用指令：\n"
                                "・設定時間 HH:MM — 變更發送時間\n"
                                "・狀態 — 查看目前設定\n"
                                "・停止 — 停止通知\n"
                                "・啟動 — 重新啟動通知"
                            ))],
                        ))
                    except Exception as e:
                        log.error("JOIN push 失敗：%s", e)

            elif isinstance(event, LeaveEvent):
                conn = get_conn(); cur = conn.cursor()
                cur.execute(
                    "UPDATE line_groups SET active = FALSE, updated_at = NOW() WHERE group_id = %s",
                    (source_id,)
                )
                conn.commit(); cur.close(); conn.close()

            elif isinstance(event, MessageEvent) and isinstance(event.message, TextMessageContent):
                text = event.message.text.strip()
                if source_type not in ("group", "room"):
                    continue

                conn = get_conn(); cur = conn.cursor()
                cur.execute("SELECT * FROM line_groups WHERE group_id = %s", (source_id,))
                group = cur.fetchone()

                if text.startswith("綁定"):
                    parts = text.split(maxsplit=1)
                    if len(parts) >= 2:
                        code = parts[1].strip().upper()
                        cur.execute("SELECT id, name FROM stores WHERE code=%s AND active=TRUE", (code,))
                        store_row = cur.fetchone()
                        if store_row:
                            if group:
                                cur.execute(
                                    "UPDATE line_groups SET store_id=%s, updated_at=NOW() WHERE group_id=%s",
                                    (store_row["id"], source_id)
                                )
                            else:
                                cur.execute(
                                    "INSERT INTO line_groups (group_id, store_id, schedule_time, active) VALUES (%s,%s,'21:00',TRUE)",
                                    (source_id, store_row["id"])
                                )
                            conn.commit()
                            _line_reply(event.reply_token,
                                f"✅ 已綁定「{store_row['name']}」\n之後將收到這家店的庫存日報與週報。",
                                fallback_to=source_id)
                        else:
                            _line_reply(event.reply_token,
                                "⚠️ 找不到此店代碼，請確認拼字（英數字，不分大小寫），可向店家管理員索取正確代碼。",
                                fallback_to=source_id)
                    else:
                        _line_reply(event.reply_token, "⚠️ 請輸入店代碼，例如：綁定 DEFAULT", fallback_to=source_id)

                elif text in ("解除綁定", "取消綁定"):
                    if group and group.get("store_id"):
                        cur.execute("UPDATE line_groups SET store_id=NULL, updated_at=NOW() WHERE group_id=%s",(source_id,))
                        conn.commit()
                        _line_reply(event.reply_token, "已解除店家綁定，之後不會再收到日報/週報，直到重新綁定。", fallback_to=source_id)
                    else:
                        _line_reply(event.reply_token, "這個群組目前沒有綁定任何店家。", fallback_to=source_id)

                elif text.startswith("設定時間"):
                    parts = text.split()
                    if len(parts) >= 2:
                        time_str = parts[1].strip()
                        try:
                            datetime.strptime(time_str, "%H:%M")
                            if group:
                                cur.execute("UPDATE line_groups SET schedule_time=%s, updated_at=NOW() WHERE group_id=%s",(time_str, source_id))
                            else:
                                cur.execute("INSERT INTO line_groups (group_id, schedule_time, active) VALUES (%s,%s,TRUE)",(source_id, time_str))
                            conn.commit()
                            _line_reply(event.reply_token, f"✅ 每日日報已設定為 {time_str} 發送。", fallback_to=source_id)
                        except ValueError:
                            _line_reply(event.reply_token, "⚠️ 時間格式錯誤，請輸入如：設定時間 09:00", fallback_to=source_id)
                    else:
                        _line_reply(event.reply_token, "⚠️ 請輸入時間，例如：設定時間 09:00", fallback_to=source_id)

                elif text.startswith("週報時間"):
                    parts = text.split()
                    if len(parts) >= 2:
                        time_str = parts[1].strip()
                        try:
                            datetime.strptime(time_str, "%H:%M")
                            if group:
                                cur.execute("UPDATE line_groups SET weekly_time=%s, updated_at=NOW() WHERE group_id=%s",(time_str, source_id))
                            else:
                                cur.execute("INSERT INTO line_groups (group_id, weekly_time, weekly_active) VALUES (%s,%s,TRUE)",(source_id, time_str))
                            conn.commit()
                            _line_reply(event.reply_token, f"✅ 每週二週報已設定為 {time_str} 發送。", fallback_to=source_id)
                        except ValueError:
                            _line_reply(event.reply_token, "⚠️ 時間格式錯誤，請輸入如：週報時間 09:00", fallback_to=source_id)
                    else:
                        _line_reply(event.reply_token, "⚠️ 請輸入時間，例如：週報時間 09:00", fallback_to=source_id)

                elif text in ("狀態", "status", "Status"):
                    if group:
                        store_label = "尚未綁定 ⚠️（請輸入「綁定 店代碼」）"
                        if group.get("store_id"):
                            cur.execute("SELECT name FROM stores WHERE id=%s", (group["store_id"],))
                            srow = cur.fetchone()
                            store_label = srow["name"] if srow else "（原綁定的店家已被刪除，請重新綁定）"
                        stime   = str(group["schedule_time"])[:5]
                        wtime   = str(group["weekly_time"])[:5] if group.get("weekly_time") else "09:00"
                        active  = "啟用 ✅" if group["active"] else "停止 ⛔"
                        wactive = "啟用 ✅" if group.get("weekly_active", True) else "停止 ⛔"
                        last    = group["last_sent_date"] or "尚未發送"
                        wlast   = group.get("last_weekly_date") or "尚未發送"
                        _line_reply(event.reply_token,
                            f"📊 目前設定\n"
                            f"──────────\n"
                            f"綁定店家：{store_label}\n"
                            f"──────────\n"
                            f"每日日報：{active}\n"
                            f"  發送時間：每天 {stime}\n"
                            f"  上次發送：{last}\n"
                            f"──────────\n"
                            f"每週消耗週報：{wactive}\n"
                            f"  發送時間：每週二 {wtime}\n"
                            f"  上次發送：{wlast}",
                            fallback_to=source_id,
                        )
                    else:
                        _line_reply(event.reply_token, "尚未設定，請先輸入「綁定 店代碼」開始使用。", fallback_to=source_id)

                elif text in ("停止", "stop", "Stop"):
                    cur.execute("UPDATE line_groups SET active=FALSE, updated_at=NOW() WHERE group_id=%s",(source_id,))
                    conn.commit()
                    _line_reply(event.reply_token, "⛔ 每日日報已停止。\n輸入「啟動」重新開啟，輸入「週報關閉」可停止週報。", fallback_to=source_id)

                elif text in ("啟動", "start", "Start"):
                    if group:
                        cur.execute("UPDATE line_groups SET active=TRUE, updated_at=NOW() WHERE group_id=%s",(source_id,))
                    else:
                        cur.execute("INSERT INTO line_groups (group_id, schedule_time, active) VALUES (%s,'21:00',TRUE)",(source_id,))
                    conn.commit()
                    _line_reply(event.reply_token, "✅ 每日日報已重新啟動！", fallback_to=source_id)

                elif text in ("週報關閉", "週報停止"):
                    cur.execute("UPDATE line_groups SET weekly_active=FALSE, updated_at=NOW() WHERE group_id=%s",(source_id,))
                    conn.commit()
                    _line_reply(event.reply_token, "⛔ 每週消耗週報已停止。輸入「週報開啟」重新啟用。", fallback_to=source_id)

                elif text in ("週報開啟", "週報啟動"):
                    if group:
                        cur.execute("UPDATE line_groups SET weekly_active=TRUE, updated_at=NOW() WHERE group_id=%s",(source_id,))
                    else:
                        cur.execute("INSERT INTO line_groups (group_id, weekly_active) VALUES (%s, TRUE)",(source_id,))
                    conn.commit()
                    _line_reply(event.reply_token, "✅ 每週消耗週報已開啟！每週二發送。", fallback_to=source_id)

                elif text in ("指令", "help", "Help", "?"):
                    _line_reply(event.reply_token,
                        "📦 StockFlow 可用指令\n"
                        "──────────\n"
                        "綁定 店代碼    綁定這個群組要接收哪家店的通知\n"
                        "解除綁定       取消目前的店家綁定\n"
                        "設定時間 HH:MM  每日日報時間\n"
                        "週報時間 HH:MM  週二週報時間\n"
                        "狀態          查看目前設定\n"
                        "停止 / 啟動   開關每日日報\n"
                        "週報關閉 / 週報開啟\n"
                        "              開關每週週報",
                        fallback_to=source_id)

                cur.close(); conn.close()

        except Exception as e:
            import logging
            logging.getLogger("stockflow").error("LINE 事件處理異常：%s", e)


# ── LINE 群組管理 API（★ Phase 2：依店過濾，綁店機制在 Phase 3）─
@app.get("/line/groups")
def list_line_groups(store: dict = Depends(require_store_admin)):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("""
        SELECT group_id, group_name,
               schedule_time::text, active,
               weekly_time::text, weekly_active,
               last_sent_date::text, last_weekly_date::text,
               joined_at::text
        FROM line_groups WHERE store_id=%s ORDER BY joined_at DESC
    """, (store["store_id"],))
    rows = [dict(r) for r in cur.fetchall()]
    cur.close(); conn.close()
    return {"groups": rows}

@app.put("/line/groups/{group_id}")
def update_line_group(
    group_id: str,
    body: LineGroupUpdateRequest,
    store: dict = Depends(require_store_admin),
):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("SELECT id FROM line_groups WHERE group_id=%s AND store_id=%s", (group_id, store["store_id"]))
    if not cur.fetchone():
        cur.close(); conn.close()
        raise HTTPException(status_code=404, detail="找不到此群組")
    if body.schedule_time  is not None: cur.execute("UPDATE line_groups SET schedule_time=%s,  updated_at=NOW() WHERE group_id=%s",(body.schedule_time,  group_id))
    if body.active         is not None: cur.execute("UPDATE line_groups SET active=%s,          updated_at=NOW() WHERE group_id=%s",(body.active,         group_id))
    if body.weekly_time    is not None: cur.execute("UPDATE line_groups SET weekly_time=%s,     updated_at=NOW() WHERE group_id=%s",(body.weekly_time,    group_id))
    if body.weekly_active  is not None: cur.execute("UPDATE line_groups SET weekly_active=%s,   updated_at=NOW() WHERE group_id=%s",(body.weekly_active,  group_id))
    conn.commit(); cur.close(); conn.close()
    return {"ok": True}

@app.delete("/line/groups/{group_id}")
def delete_line_group(group_id: str, store: dict = Depends(require_store_admin)):
    conn = get_conn(); cur = conn.cursor()
    cur.execute("DELETE FROM line_groups WHERE group_id=%s AND store_id=%s RETURNING id", (group_id, store["store_id"]))
    deleted = cur.fetchone(); conn.commit(); cur.close(); conn.close()
    if not deleted:
        raise HTTPException(status_code=404, detail="找不到此群組")
    return {"ok": True}

# ── HEALTH ────────────────────────────────────────────────────
@app.get("/health")
def health():
    return {"status": "ok", "time": datetime.now(timezone.utc).isoformat()}