"""
电商产品定价计算系统 - Flask 后端
"""

from functools import wraps
import hashlib
import os

import pymysql
from flask import Flask, g, jsonify, render_template, request, session
from werkzeug.security import check_password_hash, generate_password_hash


app = Flask(__name__)
app.config["SECRET_KEY"] = os.getenv("PRICING_SECRET_KEY", "ecommerce-pricing-local-secret")
app.config["DB_HOST"] = os.getenv("PRICING_DB_HOST", "175.27.229.243")
app.config["DB_PORT"] = int(os.getenv("PRICING_DB_PORT", "3306"))
app.config["DB_USER"] = os.getenv("PRICING_DB_USER", "root")
app.config["DB_PASSWORD"] = os.getenv("PRICING_DB_PASSWORD", "MySql@2026")
app.config["DB_NAME"] = os.getenv("PRICING_DB_NAME", "ecommerce_pricing")

DEFAULT_IP_RATE = 0.06
DEFAULT_PLATFORM_RATE = 0.095
DEFAULT_TRADE_SHARE_RATE = 0.04
DEFAULT_TEAM_RATE = 0.10
DEFAULT_MARKETING_RATE = 0.10
DEFAULT_SHIPPING = 5.5
DEFAULT_DISCOUNTS = [0.72, 0.80, 0.82, 0.85, 0.90]


def connect_mysql(database=None):
    return pymysql.connect(
        host=app.config["DB_HOST"],
        port=app.config["DB_PORT"],
        user=app.config["DB_USER"],
        password=app.config["DB_PASSWORD"],
        database=database,
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=False,
    )


def get_db():
    if "db" not in g:
        g.db = connect_mysql(app.config["DB_NAME"])
    return g.db


@app.teardown_appcontext
def close_db(exception):
    db = g.pop("db", None)
    if db is not None:
        db.close()


def init_db():
    try:
        conn = connect_mysql()
        try:
            with conn.cursor() as cursor:
                cursor.execute(
                    f"CREATE DATABASE IF NOT EXISTS `{app.config['DB_NAME']}` "
                    "CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
                )
            conn.commit()
        finally:
            conn.close()
    except pymysql.err.OperationalError:
        # Production commonly uses a least-privilege user scoped to an existing DB.
        pass

    conn = connect_mysql(app.config["DB_NAME"])
    try:
        with conn.cursor() as cursor:
            cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS users (
                    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                    username VARCHAR(80) NOT NULL UNIQUE,
                    password_hash VARCHAR(255) NOT NULL,
                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                """
            )
            cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS products (
                    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                    user_id BIGINT UNSIGNED NOT NULL,
                    name VARCHAR(255) NOT NULL,
                    factory_price DOUBLE NOT NULL DEFAULT 0,
                    production_cost DOUBLE NOT NULL DEFAULT 0,
                    suggested_retail_price DOUBLE NOT NULL DEFAULT 0,
                    gb_settlement_price DOUBLE NOT NULL DEFAULT 0,
                    remark VARCHAR(500) NOT NULL DEFAULT '',
                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX idx_products_user_name (user_id, name),
                    CONSTRAINT fk_products_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                """
            )
            cursor.execute(
                """
                CREATE TABLE IF NOT EXISTS pricing_scenarios (
                    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
                    product_id BIGINT UNSIGNED NOT NULL,
                    scenario_name VARCHAR(80) NOT NULL DEFAULT '',
                    suggested_retail_price DOUBLE NOT NULL DEFAULT 0,
                    discount_rate DOUBLE NOT NULL DEFAULT 1,
                    actual_price DOUBLE NOT NULL DEFAULT 0,
                    ip_rate DOUBLE NOT NULL DEFAULT 0.06,
                    platform_rate DOUBLE NOT NULL DEFAULT 0.095,
                    marketing_rate DOUBLE NOT NULL DEFAULT 0.10,
                    trade_share_rate DOUBLE NOT NULL DEFAULT 0.04,
                    team_rate DOUBLE NOT NULL DEFAULT 0.10,
                    ip_fee DOUBLE NOT NULL DEFAULT 0,
                    platform_fee DOUBLE NOT NULL DEFAULT 0,
                    tax DOUBLE NOT NULL DEFAULT 0,
                    shipping DOUBLE NOT NULL DEFAULT 0,
                    marketing_fee DOUBLE NOT NULL DEFAULT 0,
                    trade_share DOUBLE NOT NULL DEFAULT 0,
                    ecommerce_cost DOUBLE NOT NULL DEFAULT 0,
                    profit DOUBLE NOT NULL DEFAULT 0,
                    profit_rate DOUBLE NOT NULL DEFAULT 0,
                    team_cost DOUBLE NOT NULL DEFAULT 0,
                    profit_after_team DOUBLE NOT NULL DEFAULT 0,
                    profit_rate_after_team DOUBLE NOT NULL DEFAULT 0,
                    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_scenarios_product (product_id),
                    CONSTRAINT fk_scenarios_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
                """
            )
        conn.commit()
    finally:
        conn.close()


init_db()


def to_float(value, default=0.0):
    if value in (None, ""):
        return default
    try:
        return float(value)
    except (TypeError, ValueError):
        return default


def serialize_row(row):
    result = {}
    for key, value in row.items():
        if hasattr(value, "isoformat"):
            result[key] = value.isoformat(sep=" ", timespec="seconds")
        else:
            result[key] = value
    return result


def current_user_id():
    return session.get("user_id")


def login_required(fn):
    @wraps(fn)
    def wrapper(*args, **kwargs):
        if not current_user_id():
            return jsonify({"error": "请先登录"}), 401
        return fn(*args, **kwargs)

    return wrapper


def verify_password(password, password_hash):
    if check_password_hash(password_hash, password):
        return True
    legacy_hash = hashlib.sha256(password.encode()).hexdigest()
    return legacy_hash == password_hash


def make_password_hash(password):
    return generate_password_hash(password, method="pbkdf2:sha256")


def calculate_pricing(data):
    production_cost = to_float(data.get("production_cost"), to_float(data.get("factory_price")))
    suggested_retail_price = to_float(data.get("suggested_retail_price"))
    discount_rate = to_float(data.get("discount_rate"), 1.0)
    actual_price = to_float(data.get("actual_price"))
    if actual_price <= 0:
        actual_price = suggested_retail_price * discount_rate
    elif suggested_retail_price > 0:
        discount_rate = actual_price / suggested_retail_price

    shipping = to_float(data.get("shipping"), DEFAULT_SHIPPING)
    ip_rate = to_float(data.get("ip_rate"), DEFAULT_IP_RATE)
    platform_rate = to_float(data.get("platform_rate"), DEFAULT_PLATFORM_RATE)
    marketing_rate = to_float(data.get("marketing_rate"), DEFAULT_MARKETING_RATE)
    trade_share_rate = to_float(data.get("trade_share_rate"), DEFAULT_TRADE_SHARE_RATE)
    team_rate = to_float(data.get("team_rate"), DEFAULT_TEAM_RATE)

    ip_fee = actual_price * ip_rate
    platform_fee = actual_price * platform_rate
    marketing_fee = actual_price * marketing_rate
    trade_share = actual_price * trade_share_rate
    taxable_income = (actual_price - production_cost) / 1.13 * 0.13
    deductible_fees = (ip_fee + platform_fee + trade_share + shipping + marketing_fee) / 1.06 * 0.06
    tax = (taxable_income - deductible_fees) * 1.06
    ecommerce_cost = production_cost + ip_fee + platform_fee + tax + shipping + marketing_fee + trade_share
    profit = actual_price - ecommerce_cost
    profit_rate = profit / actual_price if actual_price > 0 else 0
    team_cost = actual_price * team_rate
    profit_after_team = profit - team_cost
    profit_rate_after_team = profit_after_team / actual_price if actual_price > 0 else 0

    return {
        "scenario_name": data.get("scenario_name") or f"{round(discount_rate * 100)}折",
        "suggested_retail_price": round(suggested_retail_price, 2),
        "discount_rate": round(discount_rate, 4),
        "actual_price": round(actual_price, 2),
        "ip_rate": round(ip_rate, 4),
        "platform_rate": round(platform_rate, 4),
        "marketing_rate": round(marketing_rate, 4),
        "trade_share_rate": round(trade_share_rate, 4),
        "team_rate": round(team_rate, 4),
        "ip_fee": round(ip_fee, 2),
        "platform_fee": round(platform_fee, 2),
        "tax": round(tax, 2),
        "shipping": round(shipping, 2),
        "marketing_fee": round(marketing_fee, 2),
        "trade_share": round(trade_share, 2),
        "ecommerce_cost": round(ecommerce_cost, 2),
        "profit": round(profit, 2),
        "profit_rate": round(profit_rate, 4),
        "team_cost": round(team_cost, 2),
        "profit_after_team": round(profit_after_team, 2),
        "profit_rate_after_team": round(profit_rate_after_team, 4),
    }


def insert_scenarios(cursor, product_id, scenarios):
    cursor.execute("DELETE FROM pricing_scenarios WHERE product_id = %s", (product_id,))
    for scenario in scenarios:
        cursor.execute(
            """
            INSERT INTO pricing_scenarios (
                product_id, scenario_name, suggested_retail_price, discount_rate, actual_price,
                ip_rate, platform_rate, marketing_rate, trade_share_rate, team_rate,
                ip_fee, platform_fee, tax, shipping, marketing_fee, trade_share,
                ecommerce_cost, profit, profit_rate, team_cost,
                profit_after_team, profit_rate_after_team
            ) VALUES (
                %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
            )
            """,
            (
                product_id,
                scenario.get("scenario_name", ""),
                to_float(scenario.get("suggested_retail_price")),
                to_float(scenario.get("discount_rate")),
                to_float(scenario.get("actual_price")),
                to_float(scenario.get("ip_rate"), DEFAULT_IP_RATE),
                to_float(scenario.get("platform_rate"), DEFAULT_PLATFORM_RATE),
                to_float(scenario.get("marketing_rate"), DEFAULT_MARKETING_RATE),
                to_float(scenario.get("trade_share_rate"), DEFAULT_TRADE_SHARE_RATE),
                to_float(scenario.get("team_rate"), DEFAULT_TEAM_RATE),
                to_float(scenario.get("ip_fee")),
                to_float(scenario.get("platform_fee")),
                to_float(scenario.get("tax")),
                to_float(scenario.get("shipping"), DEFAULT_SHIPPING),
                to_float(scenario.get("marketing_fee")),
                to_float(scenario.get("trade_share")),
                to_float(scenario.get("ecommerce_cost")),
                to_float(scenario.get("profit")),
                to_float(scenario.get("profit_rate")),
                to_float(scenario.get("team_cost")),
                to_float(scenario.get("profit_after_team")),
                to_float(scenario.get("profit_rate_after_team")),
            ),
        )


@app.route("/api/register", methods=["POST"])
def register():
    data = request.json or {}
    username = data.get("username", "").strip()
    password = data.get("password", "")
    if not username or not password:
        return jsonify({"error": "用户名和密码不能为空"}), 400
    if len(password) < 6:
        return jsonify({"error": "密码至少需要 6 位"}), 400

    db = get_db()
    with db.cursor() as cursor:
        cursor.execute("SELECT id FROM users WHERE username = %s", (username,))
        if cursor.fetchone():
            return jsonify({"error": "用户名已存在"}), 400
        cursor.execute(
            "INSERT INTO users (username, password_hash) VALUES (%s, %s)",
            (username, make_password_hash(password)),
        )
        user_id = cursor.lastrowid
    db.commit()
    session.clear()
    session["user_id"] = user_id
    session["username"] = username
    return jsonify({"success": True, "user": {"id": user_id, "username": username}})


@app.route("/api/login", methods=["POST"])
def login():
    data = request.json or {}
    username = data.get("username", "").strip()
    password = data.get("password", "")
    db = get_db()
    with db.cursor() as cursor:
        cursor.execute("SELECT id, username, password_hash FROM users WHERE username = %s", (username,))
        user = cursor.fetchone()
        if not user or not verify_password(password, user["password_hash"]):
            return jsonify({"error": "用户名或密码错误"}), 401
        if len(user["password_hash"]) == 64:
            cursor.execute(
                "UPDATE users SET password_hash = %s WHERE id = %s",
                (make_password_hash(password), user["id"]),
            )
            db.commit()

    session.clear()
    session["user_id"] = user["id"]
    session["username"] = user["username"]
    return jsonify({"success": True, "user": {"id": user["id"], "username": user["username"]}})


@app.route("/api/logout", methods=["POST"])
def logout():
    session.clear()
    return jsonify({"success": True})


@app.route("/api/me", methods=["GET"])
def me():
    if not current_user_id():
        return jsonify({"user": None})
    return jsonify({"user": {"id": session["user_id"], "username": session.get("username", "")}})


@app.route("/api/products", methods=["GET"])
@login_required
def get_products():
    keyword = request.args.get("q", "").strip()
    params = [current_user_id()]
    where = "WHERE p.user_id = %s"
    if keyword:
        where += " AND p.name LIKE %s"
        params.append(f"%{keyword}%")

    db = get_db()
    with db.cursor() as cursor:
        cursor.execute(
            f"""
            SELECT p.*, COUNT(ps.id) AS scenario_count
            FROM products p
            LEFT JOIN pricing_scenarios ps ON p.id = ps.product_id
            {where}
            GROUP BY p.id
            ORDER BY p.updated_at DESC, p.id DESC
            """,
            params,
        )
        products = [serialize_row(row) for row in cursor.fetchall()]
    return jsonify(products)


@app.route("/api/products", methods=["POST"])
@login_required
def create_product():
    data = request.json or {}
    name = data.get("name", "").strip()
    factory_price = to_float(data.get("factory_price"))
    production_cost = to_float(data.get("production_cost"), factory_price)
    suggested_retail_price = to_float(data.get("suggested_retail_price"))
    if not name:
        return jsonify({"error": "产品名称不能为空"}), 400
    if factory_price <= 0:
        return jsonify({"error": "工厂采购价必须大于 0"}), 400
    if suggested_retail_price <= 0:
        return jsonify({"error": "建议零售价必须大于 0"}), 400

    scenarios = data.get("scenarios") or []
    db = get_db()
    with db.cursor() as cursor:
        cursor.execute(
            """
            INSERT INTO products (
                user_id, name, factory_price, production_cost,
                suggested_retail_price, gb_settlement_price, remark
            ) VALUES (%s, %s, %s, %s, %s, %s, %s)
            """,
            (
                current_user_id(),
                name,
                factory_price,
                production_cost,
                suggested_retail_price,
                to_float(data.get("gb_settlement_price")),
                data.get("remark", "").strip(),
            ),
        )
        product_id = cursor.lastrowid
        insert_scenarios(cursor, product_id, scenarios)
    db.commit()
    return jsonify({"success": True, "id": product_id, "name": name})


@app.route("/api/products/<int:product_id>", methods=["PUT"])
@login_required
def update_product(product_id):
    data = request.json or {}
    name = data.get("name", "").strip()
    factory_price = to_float(data.get("factory_price"))
    production_cost = to_float(data.get("production_cost"), factory_price)
    suggested_retail_price = to_float(data.get("suggested_retail_price"))
    if not name:
        return jsonify({"error": "产品名称不能为空"}), 400
    if factory_price <= 0:
        return jsonify({"error": "工厂采购价必须大于 0"}), 400
    if suggested_retail_price <= 0:
        return jsonify({"error": "建议零售价必须大于 0"}), 400

    scenarios = data.get("scenarios") or []
    db = get_db()
    with db.cursor() as cursor:
        cursor.execute(
            """
            UPDATE products
            SET name = %s,
                factory_price = %s,
                production_cost = %s,
                suggested_retail_price = %s,
                gb_settlement_price = %s,
                remark = %s
            WHERE id = %s AND user_id = %s
            """,
            (
                name,
                factory_price,
                production_cost,
                suggested_retail_price,
                to_float(data.get("gb_settlement_price")),
                data.get("remark", "").strip(),
                product_id,
                current_user_id(),
            ),
        )
        if cursor.rowcount == 0:
            return jsonify({"error": "产品不存在"}), 404
        insert_scenarios(cursor, product_id, scenarios)
    db.commit()
    return jsonify({"success": True, "id": product_id, "name": name})


@app.route("/api/products/<int:product_id>", methods=["GET"])
@login_required
def get_product(product_id):
    db = get_db()
    with db.cursor() as cursor:
        cursor.execute(
            "SELECT * FROM products WHERE id = %s AND user_id = %s",
            (product_id, current_user_id()),
        )
        product = cursor.fetchone()
        if not product:
            return jsonify({"error": "产品不存在"}), 404
        cursor.execute(
            "SELECT * FROM pricing_scenarios WHERE product_id = %s ORDER BY discount_rate ASC, id ASC",
            (product_id,),
        )
        scenarios = [serialize_row(row) for row in cursor.fetchall()]
    result = serialize_row(product)
    result["scenarios"] = scenarios
    return jsonify(result)


@app.route("/api/products/<int:product_id>", methods=["DELETE"])
@login_required
def delete_product(product_id):
    db = get_db()
    with db.cursor() as cursor:
        cursor.execute(
            "DELETE FROM products WHERE id = %s AND user_id = %s",
            (product_id, current_user_id()),
        )
        deleted = cursor.rowcount
    db.commit()
    if not deleted:
        return jsonify({"error": "产品不存在"}), 404
    return jsonify({"success": True})


@app.route("/api/products/<int:product_id>/scenarios", methods=["POST"])
@login_required
def save_scenarios(product_id):
    data = request.json or {}
    scenarios = data.get("scenarios", [])
    db = get_db()
    with db.cursor() as cursor:
        cursor.execute(
            "SELECT id FROM products WHERE id = %s AND user_id = %s",
            (product_id, current_user_id()),
        )
        if not cursor.fetchone():
            return jsonify({"error": "产品不存在"}), 404
        insert_scenarios(cursor, product_id, scenarios)
    db.commit()
    return jsonify({"success": True})


@app.route("/api/calculate", methods=["POST"])
def calculate():
    data = request.json or {}
    return jsonify(calculate_pricing(data))


@app.route("/api/defaults", methods=["GET"])
def defaults():
    return jsonify(
        {
            "discounts": DEFAULT_DISCOUNTS,
            "shipping": DEFAULT_SHIPPING,
            "marketing_rate": DEFAULT_MARKETING_RATE,
            "ip_rate": DEFAULT_IP_RATE,
            "platform_rate": DEFAULT_PLATFORM_RATE,
            "trade_share_rate": DEFAULT_TRADE_SHARE_RATE,
            "team_rate": DEFAULT_TEAM_RATE,
        }
    )


@app.route("/")
def index():
    return render_template("index.html")


if __name__ == "__main__":
    app.run(debug=False, host="0.0.0.0", port=5000)