# ==============================================================================
# 						API SERVER LENGKAP UNTUK APLIKASI KASIR
# ==============================================================================
# Deskripsi:
# Versi ini telah memperbaiki endpoint export untuk menyertakan 'tipe_produk'
# yang dibutuhkan oleh modul Laporan Keuangan.
# ==============================================================================

import os
from datetime import datetime, timedelta
import mysql.connector
from mysql.connector import Error
from flask import Flask, jsonify, request, send_from_directory
from flask_cors import CORS
from dotenv import load_dotenv
import json
import uuid
import bcrypt 
from functools import wraps
from zoneinfo import ZoneInfo
import logging
from logging.handlers import RotatingFileHandler

# --- KONFIGURASI LOGGING ---
log_file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'api_server.log')
log_formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(funcName)s:%(lineno)d - %(message)s')
file_handler = RotatingFileHandler(log_file_path, maxBytes=5*1024*1024, backupCount=5, encoding='utf-8')
file_handler.setFormatter(log_formatter)
console_handler = logging.StreamHandler()
console_handler.setFormatter(log_formatter)

app_logger = logging.getLogger()
app_logger.setLevel(logging.WARNING) # Catat dari level WARNING, ERROR, dan CRITICAL
app_logger.addHandler(file_handler)
app_logger.addHandler(console_handler)

load_dotenv()

# --- PENYIMPANAN QUERY SQL ---
KARYAWAN_QUERIES = {
    "get_all": "SELECT k.id_karyawan, k.nama, k.email, j.nama_jabatan, ul.username FROM karyawan k JOIN jabatan j ON k.jabatan_id = j.id_jabatan LEFT JOIN user_login ul ON k.id_karyawan = ul.karyawan_id WHERE k.nama LIKE %s OR k.email LIKE %s OR j.nama_jabatan LIKE %s ORDER BY k.id_karyawan",
    "check_username": "SELECT username FROM user_login WHERE username = %s",
    "add_karyawan": "INSERT INTO karyawan (nama, email, jabatan_id) VALUES (%s, %s, %s)",
    "add_user_login": "INSERT INTO user_login (username, password_hash, karyawan_id) VALUES (%s, %s, %s)",
    "update_karyawan": "UPDATE karyawan SET nama = %s, email = %s, jabatan_id = %s WHERE id_karyawan = %s",
    "check_login_exists": "SELECT karyawan_id FROM user_login WHERE karyawan_id = %s",
    "update_user_login_with_password": "UPDATE user_login SET username = %s, password_hash = %s WHERE karyawan_id = %s",
    "update_user_login_without_password": "UPDATE user_login SET username = %s WHERE karyawan_id = %s",
    "delete_user_login": "DELETE FROM user_login WHERE karyawan_id = %s",
    "delete_karyawan": "DELETE FROM karyawan WHERE id_karyawan = %s"
}
JABATAN_QUERIES = {"get_all": "SELECT id_jabatan, nama_jabatan FROM jabatan ORDER BY nama_jabatan ASC"}
DASHBOARD_QUERIES = {
    "sales_summary": "SELECT SUM(p.harga_jual * dp.jumlah) AS total_penjualan, SUM(p.harga_beli * dp.jumlah) AS total_harga_pokok FROM detail_penjualan dp JOIN produk p ON dp.id_produk = p.id_produk JOIN penjualan pj ON dp.id_penjualan = pj.id_penjualan WHERE pj.status_penjualan = 'selesai'",
    "transaction_summary": "SELECT COUNT(id_penjualan) AS total_transaksi FROM penjualan WHERE status_penjualan = 'selesai'",
    "top_selling_products": "SELECT p.nama_produk, SUM(dp.jumlah) AS total_terjual FROM detail_penjualan dp JOIN produk p ON dp.id_produk = p.id_produk JOIN penjualan pj ON dp.id_penjualan = pj.id_penjualan WHERE pj.status_penjualan = 'selesai' GROUP BY p.nama_produk ORDER BY total_terjual DESC LIMIT 5",
    "low_stock_products": "SELECT nama_produk, stok_saat_ini, stok_minimal FROM produk WHERE stok_saat_ini <= stok_minimal AND is_active = TRUE ORDER BY stok_saat_ini ASC",
    "total_products": "SELECT COUNT(id_produk) AS total_produk FROM produk WHERE is_active = TRUE"
}
app = Flask(__name__)
CORS(app)
WIB = ZoneInfo('Asia/Jakarta')
SECRET_API_KEY = os.getenv('SECRET_API_KEY', 'RAHASIA123-INIKUNCIAPI-SANGATAMAN-XYZ')
DB_CONFIG = {
    'user': os.getenv('DB_USER', 'yosefdav_yosefdavid13'),
    'password': os.getenv('DB_PASSWORD', 'yosefdavid13'),
    'host': os.getenv('DB_HOST', '103.16.199.3'),
    'database': os.getenv('DB_NAME', 'yosefdav_tbmaju')
}
IMAGE_FOLDER = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'product_images')
if not os.path.exists(IMAGE_FOLDER):
    os.makedirs(IMAGE_FOLDER)

def get_db_connection(transactional=False):
    try:
        return mysql.connector.connect(**DB_CONFIG, connection_timeout=10, autocommit=not transactional)
    except Error as e:
        app_logger.critical(f"Koneksi database GAGAL. Detail: {e}", exc_info=True)
        return None

def format_rupiah(amount):
    try:
        return f"Rp{float(amount):,.2f}".replace(",", "#").replace(".", ",").replace("#", ".")
    except (ValueError, TypeError):
        return str(amount)

def require_api_key(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        api_key = request.headers.get('X-API-Key')
        if api_key and api_key == SECRET_API_KEY:
            return f(*args, **kwargs)
        else:
            app_logger.warning(f"Akses ditolak ke endpoint '{request.path}' dari IP {request.remote_addr} karena API Key tidak valid.")
            return jsonify({"message": "Unauthorized: Invalid or missing API Key"}), 401
    return decorated
    
@app.before_request
def log_request_info():
    if request.path.startswith('/api/'):
        app_logger.info(f"Request Diterima: {request.method} {request.path} dari IP {request.remote_addr}")

@app.route("/api/login", methods=["POST"])
def api_login():
    conn = None
    cursor = None
    try:
        data = request.get_json()
        if not data or not data.get('username') or not data.get('password'):
            return jsonify({"success": False, "error": "Username dan password harus diisi."}), 400
        username = data['username']
        password = data['password']
        conn = get_db_connection()
        if not conn:
            return jsonify({"success": False, "error": "Gagal terhubung ke server database."}), 500
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT username, password_hash, karyawan_id FROM user_login WHERE username = %s", (username,))
        user_data = cursor.fetchone()
        if not user_data:
            app_logger.warning(f"Percobaan login gagal untuk username '{username}': tidak ditemukan.")
            return jsonify({"success": False, "error": "Username tidak ditemukan."}), 404
        stored_hash = user_data['password_hash'].encode('utf-8')
        if not bcrypt.checkpw(password.encode('utf-8'), stored_hash):
            app_logger.warning(f"Percobaan login gagal untuk username '{username}': password salah.")
            return jsonify({"success": False, "error": "Password yang Anda masukkan salah."}), 401
        karyawan_id = user_data.get('karyawan_id')
        if not karyawan_id:
            return jsonify({"success": False, "error": "Akun user tidak terhubung dengan data karyawan."}), 500
        cursor.execute("SELECT k.nama, j.nama_jabatan FROM karyawan k LEFT JOIN jabatan j ON k.jabatan_id = j.id_jabatan WHERE k.id_karyawan = %s", (karyawan_id,))
        karyawan_data = cursor.fetchone()
        if not karyawan_data:
            return jsonify({"success": False, "error": f"Data karyawan untuk ID {karyawan_id} tidak ditemukan."}), 404
        
        app_logger.info(f"Login berhasil untuk user '{username}' sebagai '{karyawan_data.get('nama_jabatan')}'.")
        return jsonify({"success": True, "user_name": karyawan_data.get('nama'), "user_role": karyawan_data.get('nama_jabatan')})
    except Exception as e:
        app_logger.error(f"Error tak terduga di endpoint /api/login: {e}", exc_info=True)
        return jsonify({"success": False, "error": "Terjadi kesalahan internal pada server."}), 500
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

# ... (Sisa kode dari /api/config/ftp hingga /api/riwayat-penjualan/cancel/<id> tidak ada perubahan)...

@app.route("/api/config/ftp", methods=["GET"])
@require_api_key
def get_ftp_config():
    ftp_config = {
        "server": os.getenv('FTP_SERVER', 'ftp.vestra.biz.id'),
        "user": os.getenv('FTP_USER', 'ftpupgambar@vestra.biz.id'),
        "password": os.getenv('FTP_PASS', 'ftpupgambar123!'),
        "remote_dir": os.getenv('FTP_REMOTE_DIR', 'product_images')
    }
    return jsonify({"success": True, "data": ftp_config})

@app.route("/api/dashboard/summary", methods=["GET"])
def get_dashboard_summary():
    conn = None
    try:
        conn = get_db_connection()
        if not conn: return jsonify({"success": False, "error": "Database connection failed"}), 500
        cursor = conn.cursor(dictionary=True)
        results = {}
        for key, query in DASHBOARD_QUERIES.items():
            cursor.execute(query)
            if "summary" in key or "total" in key:
                results[key] = cursor.fetchone()
            else:
                results[key] = cursor.fetchall()
        return jsonify({"success": True, "data": results})
    except Error as e:
        app_logger.error(f"Error saat mengambil data dashboard: {e}", exc_info=True)
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()

@app.route("/api/karyawan", methods=["GET"])
@require_api_key
def get_all_karyawan():
    conn = None
    try:
        conn = get_db_connection()
        if not conn: return jsonify({"success": False, "error": "Database connection failed"}), 500
        cursor = conn.cursor(dictionary=True)
        keyword = request.args.get('keyword', '')
        search_pattern = f"%{keyword}%"
        cursor.execute(KARYAWAN_QUERIES['get_all'], (search_pattern, search_pattern, search_pattern))
        karyawan_list = cursor.fetchall()
        return jsonify({"success": True, "data": karyawan_list})
    except Error as e:
        app_logger.error(f"Error saat mengambil data semua karyawan: {e}", exc_info=True)
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/karyawan", methods=["POST"])
@require_api_key
def add_karyawan():
    conn = None
    data = request.json
    try:
        conn = get_db_connection(transactional=True)
        if not conn: return jsonify({"success": False, "error": "Database connection failed"}), 500
        cursor = conn.cursor()
        cursor.execute(KARYAWAN_QUERIES['check_username'], (data['username'],))
        if cursor.fetchone():
            return jsonify({"success": False, "error": "Username already exists"}), 409
        cursor.execute(KARYAWAN_QUERIES['add_karyawan'], (data['nama'], data['email'], data['jabatan_id']))
        karyawan_id = cursor.lastrowid
        hashed_password = bcrypt.hashpw(data['password'].encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
        cursor.execute(KARYAWAN_QUERIES['add_user_login'], (data['username'], hashed_password, karyawan_id))
        conn.commit()
        return jsonify({"success": True, "message": "Employee and login created successfully", "karyawan_id": karyawan_id}), 201
    except Error as e:
        if conn: conn.rollback()
        app_logger.error(f"Error saat menambah karyawan baru: {e}", exc_info=True)
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/karyawan/<int:karyawan_id>", methods=["PUT"])
@require_api_key
def update_karyawan(karyawan_id):
    conn = None
    data = request.json
    try:
        conn = get_db_connection(transactional=True)
        if not conn: return jsonify({"success": False, "error": "Database connection failed"}), 500
        cursor = conn.cursor()
        cursor.execute(KARYAWAN_QUERIES['update_karyawan'], (data['nama'], data['email'], data['jabatan_id'], karyawan_id))
        cursor.execute(KARYAWAN_QUERIES['check_login_exists'], (karyawan_id,))
        login_exists = cursor.fetchone()
        if login_exists:
            if data.get('password') and data['password']:
                hashed = bcrypt.hashpw(data['password'].encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
                cursor.execute(KARYAWAN_QUERIES['update_user_login_with_password'], (data['username'], hashed, karyawan_id))
            else:
                cursor.execute(KARYAWAN_QUERIES['update_user_login_without_password'], (data['username'], karyawan_id))
        elif data.get('username') and data.get('password'):
            hashed = bcrypt.hashpw(data['password'].encode('utf-8'), bcrypt.gensalt()).decode('utf-8')
            cursor.execute(KARYAWAN_QUERIES['add_user_login'], (data['username'], hashed, karyawan_id))
        conn.commit()
        return jsonify({"success": True, "message": "Employee updated successfully"})
    except Error as e:
        if conn: conn.rollback()
        app_logger.error(f"Error saat update karyawan ID {karyawan_id}: {e}", exc_info=True)
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/karyawan/<int:karyawan_id>", methods=["DELETE"])
@require_api_key
def delete_karyawan(karyawan_id):
    conn = None
    try:
        conn = get_db_connection(transactional=True)
        if not conn: return jsonify({"success": False, "error": "Database connection failed"}), 500
        cursor = conn.cursor()
        cursor.execute(KARYAWAN_QUERIES['delete_user_login'], (karyawan_id,))
        cursor.execute(KARYAWAN_QUERIES['delete_karyawan'], (karyawan_id,))
        conn.commit()
        if cursor.rowcount == 0:
            return jsonify({"success": False, "error": "Employee not found"}), 404
        return jsonify({"success": True, "message": "Employee deleted successfully"})
    except Error as e:
        if conn: conn.rollback()
        app_logger.error(f"Error saat hapus karyawan ID {karyawan_id}: {e}", exc_info=True)
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/jabatan", methods=["GET"])
@require_api_key
def get_all_jabatan():
    conn = None
    try:
        conn = get_db_connection()
        if not conn: return jsonify({"success": False, "error": "Database connection failed"}), 500
        cursor = conn.cursor(dictionary=True)
        cursor.execute(JABATAN_QUERIES['get_all'])
        jabatan_list = cursor.fetchall()
        return jsonify({"success": True, "data": jabatan_list})
    except Error as e:
        app_logger.error(f"Error saat mengambil data jabatan: {e}", exc_info=True)
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/produk/all", methods=["GET"])
@require_api_key
def get_all_products_complete():
    conn = None
    try:
        conn = get_db_connection()
        if not conn: return jsonify({"success": False, "error": "Database connection failed"}), 500
        cursor = conn.cursor(dictionary=True)
        query = "SELECT id_produk, kode_produk, nama_produk, satuan, tipe_produk, stok_saat_ini, stok_minimal, harga_beli, harga_jual, gambar_produk, deskripsi_produk FROM produk WHERE is_active = TRUE ORDER BY id_produk"
        cursor.execute(query)
        produk = cursor.fetchall()
        return jsonify({"success": True, "data": produk})
    except Error as e:
        app_logger.error(f"Error saat mengambil semua data produk (lengkap): {e}", exc_info=True)
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/produk/<int:product_id>", methods=["GET"])
@require_api_key
def get_product_by_id(product_id):
    conn = None
    try:
        conn = get_db_connection()
        if not conn: return jsonify({"success": False, "error": "Database connection failed"}), 500
        cursor = conn.cursor(dictionary=True)
        query = "SELECT * FROM produk WHERE id_produk = %s"
        cursor.execute(query, (product_id,))
        produk = cursor.fetchone()
        if produk:
            return jsonify({"success": True, "data": produk})
        else:
            return jsonify({"success": False, "error": "Product not found"}), 404
    except Error as e:
        app_logger.error(f"Error saat mengambil produk by ID {product_id}: {e}", exc_info=True)
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/produk/<int:product_id>", methods=["PUT"])
@require_api_key
def update_product(product_id):
    conn = None
    data = request.json
    try:
        conn = get_db_connection(transactional=True)
        if not conn: return jsonify({"success": False, "error": "Database connection failed"}), 500
        cursor = conn.cursor()
        query = """UPDATE produk SET 
                       nama_produk = %s, satuan = %s, tipe_produk = %s, 
                       stok_saat_ini = %s, stok_minimal = %s, harga_beli = %s, 
                       harga_jual = %s, gambar_produk = %s, deskripsi_produk = %s 
                       WHERE id_produk = %s"""
        params = (
            data.get('nama_produk'), data.get('satuan'), data.get('tipe_produk'),
            data.get('stok_saat_ini'), data.get('stok_minimal'), data.get('harga_beli'),
            data.get('harga_jual'), data.get('gambar_produk'), data.get('deskripsi_produk'),
            product_id
        )
        cursor.execute(query, params)
        conn.commit()
        return jsonify({"success": True, "message": "Product updated successfully"})
    except Error as e:
        if conn: conn.rollback()
        app_logger.error(f"Error saat update produk ID {product_id}: {e}", exc_info=True)
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/produk/<int:product_id>", methods=["DELETE"])
@require_api_key
def deactivate_product(product_id):
    conn = None
    try:
        conn = get_db_connection(transactional=True)
        if not conn: return jsonify({"success": False, "error": "Database connection failed"}), 500
        cursor = conn.cursor()
        query = "UPDATE produk SET is_active = FALSE WHERE id_produk = %s"
        cursor.execute(query, (product_id,))
        conn.commit()
        if cursor.rowcount == 0:
            return jsonify({"success": False, "error": "Product not found"}), 404
        return jsonify({"success": True, "message": "Product deactivated successfully"})
    except Error as e:
        if conn: conn.rollback()
        app_logger.error(f"Error saat nonaktifkan produk ID {product_id}: {e}", exc_info=True)
        return jsonify({"success": False, "error": str(e)}), 500
    finally:
        if conn and conn.is_connected(): conn.close()
        
@app.route("/api/produk", methods=["POST"])
@require_api_key
def add_produk():
    conn = None
    data = request.json
    try:
        conn = get_db_connection(transactional=True)
        if not conn: return jsonify({"success": False, "error": "Tidak bisa terhubung ke database"}), 500
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT kode_produk FROM produk FOR UPDATE")
        existing_codes = {row['kode_produk'].lower() for row in cursor.fetchall() if row.get('kode_produk')}
        suggested_code = data.get('kode_produk')
        final_kode_produk = suggested_code
        if not final_kode_produk or final_kode_produk.lower() in existing_codes:
            next_num = 1
            if existing_codes:
                max_num = max([int(c[4:]) for c in existing_codes if c.lower().startswith('prod') and c[4:].isdigit()] + [0])
                next_num = max_num + 1
            while f"PROD{next_num:03d}".lower() in existing_codes:
                next_num += 1
            final_kode_produk = f"PROD{next_num:03d}"
        query = "INSERT INTO produk (kode_produk, nama_produk, satuan, stok_saat_ini, stok_minimal, harga_beli, harga_jual, gambar_produk, tipe_produk, deskripsi_produk) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        data_tuple = (final_kode_produk, data['nama_produk'], data['satuan'], data['stok_saat_ini'], data['stok_minimal'], data['harga_beli'], data['harga_jual'], data.get('gambar_produk'), data.get('tipe_produk'), data.get('deskripsi_produk'))
        cursor.execute(query, data_tuple)
        conn.commit()
        return jsonify({"success": True, "message": "Produk berhasil ditambahkan.", "kode_produk_final": final_kode_produk}), 201
    except Error as e:
        if conn: conn.rollback()
        app_logger.error(f"Gagal menambahkan produk: {e}", exc_info=True)
        return jsonify({"success": False, "error": f"Gagal menambahkan produk: {e}"}), 500
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()

@app.route("/api/produk", methods=["GET"])
def get_all_produk_kasir():
    conn = None
    try:
        conn = get_db_connection()
        if not conn: return jsonify({"success": False, "error": "Tidak bisa terhubung ke database"}), 500
        cursor = conn.cursor(dictionary=True)
        sql_query = "SELECT id_produk, kode_produk, nama_produk, harga_jual, stok_saat_ini, gambar_produk, satuan, tipe_produk, deskripsi_produk FROM produk WHERE is_active = TRUE ORDER BY nama_produk"
        cursor.execute(sql_query)
        produk_list = cursor.fetchall()
        return jsonify({"success": True, "data": produk_list})
    except Error as e:
        app_logger.error(f"Error saat mengambil produk untuk kasir: {e}", exc_info=True)
        return jsonify({"success": False, "error": "Gagal mengambil data produk"}), 500
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()

@app.route("/api/produk/search", methods=['GET'])
def search_produk():
    keyword = request.args.get('keyword', '').lower()
    conn = None
    try:
        conn = get_db_connection()
        if conn is None: return jsonify({'success': False, 'error': 'Tidak bisa terhubung ke database'}), 500
        cursor = conn.cursor(dictionary=True)
        query = "SELECT id_produk, kode_produk, nama_produk, harga_jual, stok_saat_ini, gambar_produk, tipe_produk, deskripsi_produk FROM produk WHERE is_active = TRUE AND (LOWER(nama_produk) LIKE %s OR LOWER(kode_produk) LIKE %s) ORDER BY nama_produk"
        cursor.execute(query, (f"%{keyword}%", f"%{keyword}%"))
        produk = cursor.fetchall()
        formatted_produk = [{**p, 'harga_jual': float(p['harga_jual']), 'harga_formatted': format_rupiah(p['harga_jual'])} for p in produk]
        return jsonify({'success': True, 'data': formatted_produk})
    except (Exception, Error) as e:
        app_logger.error(f"Error saat mencari produk: {e}", exc_info=True)
        return jsonify({'success': False, 'error': f"Gagal mencari produk: {e}"}), 500
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()

@app.route("/api/riwayat-penjualan", methods=["GET"])
def get_riwayat_penjualan():
    conn = None
    try:
        conn = get_db_connection()
        if conn is None: return jsonify({"success": False, "error": "Tidak bisa terhubung ke database"}), 500
        cursor = conn.cursor(dictionary=True)
        keyword = request.args.get('keyword', '')
        from_date_str = request.args.get('from_date', '')
        to_date_str = request.args.get('to_date', '')
        payment_method = request.args.get('payment_method', '')
        
        base_query = "SELECT id_penjualan, updated_at AS tgl_transaksi, total_harga, jumlah_item, total_bayar, kembalian, status_penjualan, metode_pembayaran FROM penjualan WHERE 1=1"
        params = []

        if keyword:
            base_query += " AND CAST(id_penjualan AS CHAR) LIKE %s"
            params.append(f"%{keyword}%")
        if from_date_str:
            base_query += " AND tgl_transaksi >= %s"
            params.append(from_date_str)
        if to_date_str:
            to_date_obj = datetime.strptime(to_date_str, '%Y-%m-%d')
            to_date_next_day = to_date_obj + timedelta(days=1)
            base_query += " AND tgl_transaksi < %s"
            params.append(to_date_next_day.strftime('%Y-%m-%d'))
        if payment_method and payment_method != "Semua":
            base_query += " AND metode_pembayaran = %s"
            params.append(payment_method)
        
        base_query += " ORDER BY tgl_transaksi DESC, id_penjualan DESC"
        
        cursor.execute(base_query, tuple(params))
        sales_records = cursor.fetchall()
        
        for record in sales_records:
            if isinstance(record['tgl_transaksi'], datetime):
                record['tgl_transaksi'] = record['tgl_transaksi'].strftime('%Y-%m-%d %H:%M')

        return jsonify({"success": True, "data": sales_records})
        
    except (Error, ValueError) as e:
        app_logger.error(f"Error saat mengambil riwayat penjualan: {e}", exc_info=True)
        return jsonify({"success": False, "error": f"Gagal mengambil riwayat penjualan: Terjadi kesalahan internal server."}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/riwayat-penjualan/details/<int:sale_id>", methods=["GET"])
def get_sale_details(sale_id):
    conn = None
    try:
        conn = get_db_connection()
        if conn is None: return jsonify({"success": False, "error": "Tidak bisa terhubung ke database"}), 500
        cursor = conn.cursor(dictionary=True)
        query = "SELECT p.nama_produk, dp.jumlah, dp.harga_jual_satuan, dp.catatan, dp.subtotal FROM detail_penjualan dp JOIN produk p ON dp.id_produk = p.id_produk WHERE dp.id_penjualan = %s"
        cursor.execute(query, (sale_id,))
        detail_records = cursor.fetchall()
        return jsonify({"success": True, "data": detail_records})
    except Error as e:
        app_logger.error(f"Error saat mengambil detail penjualan ID {sale_id}: {e}", exc_info=True)
        return jsonify({"success": False, "error": f"Gagal mengambil detail: {e}"}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/riwayat-penjualan/cancel/<int:sale_id>", methods=["POST"])
def cancel_sale(sale_id):
    conn = None
    try:
        conn = get_db_connection(transactional=True)
        if conn is None: return jsonify({"success": False, "error": "Tidak bisa terhubung ke database"}), 500
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT status_penjualan FROM penjualan WHERE id_penjualan = %s", (sale_id,))
        sale = cursor.fetchone()
        if not sale:
            return jsonify({"success": False, "error": "Transaksi tidak ditemukan."}), 404
        if sale['status_penjualan'] == 'dibatalkan':
            return jsonify({"success": False, "error": "Transaksi ini sudah dibatalkan sebelumnya."}), 400
        cursor.execute("SELECT id_produk, jumlah FROM detail_penjualan WHERE id_penjualan = %s", (sale_id,))
        items_to_restock = cursor.fetchall()
        for item in items_to_restock:
            cursor.execute("UPDATE produk SET stok_saat_ini = stok_saat_ini + %s WHERE id_produk = %s", (item['jumlah'], item['id_produk']))
        cursor.execute("UPDATE penjualan SET status_penjualan = 'dibatalkan', updated_at = NOW() WHERE id_penjualan = %s", (sale_id,))
        conn.commit()
        return jsonify({"success": True, "message": f"Transaksi ID {sale_id} berhasil dibatalkan."})
    except Error as e:
        if conn: conn.rollback()
        app_logger.error(f"Error saat membatalkan transaksi ID {sale_id}: {e}", exc_info=True)
        return jsonify({"success": False, "error": f"Gagal membatalkan transaksi: {e}"}), 500
    finally:
        if conn and conn.is_connected(): conn.close()
        
@app.route("/api/riwayat-penjualan/export", methods=["GET"])
def export_riwayat_penjualan():
    conn = None
    try:
        conn = get_db_connection()
        if conn is None:
            return jsonify({"success": False, "error": "Tidak bisa terhubung ke database"}), 500
        cursor = conn.cursor(dictionary=True)
        
        from_date_str = request.args.get('from_date', '')
        to_date_str = request.args.get('to_date', '')
        
        # ### PERBAIKAN UTAMA ADA DI SINI ###
        # Query ini sekarang mengambil 'tipe_produk' dari tabel produk.
        base_query = """
            SELECT 
                pj.id_penjualan, pj.updated_at AS tgl_transaksi, pj.total_harga, 
                pj.jumlah_item AS total_items_in_sale, pj.total_bayar, pj.kembalian, 
                pj.status_penjualan, pj.metode_pembayaran, p.nama_produk, 
                p.tipe_produk, -- <--- KOLOM INI DITAMBAHKAN
                dp.jumlah, p.harga_beli, dp.harga_jual_satuan, dp.catatan, dp.subtotal 
            FROM penjualan pj 
            LEFT JOIN detail_penjualan dp ON pj.id_penjualan = dp.id_penjualan 
            LEFT JOIN produk p ON dp.id_produk = p.id_produk 
            WHERE 1=1
        """
        params = []
        
        if from_date_str:
            base_query += " AND pj.tgl_transaksi >= %s"
            params.append(from_date_str)
        if to_date_str:
            to_date_obj = datetime.strptime(to_date_str, '%Y-%m-%d')
            to_date_next_day = to_date_obj + timedelta(days=1)
            base_query += " AND pj.tgl_transaksi < %s"
            params.append(to_date_next_day.strftime('%Y-%m-%d'))
            
        base_query += " ORDER BY pj.tgl_transaksi DESC, pj.id_penjualan DESC"
        
        cursor.execute(base_query, tuple(params))
        all_records = cursor.fetchall()

        # Konversi datetime ke string agar aman untuk JSON
        for record in all_records:
            if isinstance(record.get('tgl_transaksi'), datetime):
                record['tgl_transaksi'] = record['tgl_transaksi'].strftime('%Y-%m-%d %H:%M')
        
        return jsonify({"success": True, "data": all_records})

    except (Error, ValueError) as e:
        app_logger.error(f"Error saat ekspor riwayat penjualan: {e}", exc_info=True)
        return jsonify({"success": False, "error": f"Gagal mengambil data ekspor: {e}"}), 500
    finally:
        if conn and conn.is_connected():
            cursor.close()
            conn.close()


@app.route("/api/sales/offline-order", methods=["POST"])
def process_offline_sale():
    conn = None
    data = request.get_json()
    if data is None or 'cart_items' not in data:
        return jsonify({"success": False, "error": "Data pesanan tidak valid"}), 400
    try:
        conn = get_db_connection(transactional=True)
        if conn is None:
            return jsonify({"success": False, "error": "Tidak bisa terhubung ke database"}), 500
        cursor = conn.cursor(dictionary=True)
        cart_items = data['cart_items']
        for item in cart_items:
            cursor.execute("SELECT stok_saat_ini, nama_produk FROM produk WHERE id_produk = %s AND is_active = TRUE FOR UPDATE", (item['id_produk'],))
            stock_result = cursor.fetchone()
            if not stock_result or item['jumlah'] > stock_result['stok_saat_ini']:
                conn.rollback()
                return jsonify({'success': False, 'error': f"Stok tidak mencukupi untuk produk '{stock_result.get('nama_produk', 'N/A')}'. Tersedia: {stock_result.get('stok_saat_ini', 0)}."}), 400
        tgl_transaksi = data.get('tgl_transaksi', datetime.now(WIB).strftime('%Y-%m-%d %H:%M:%S'))
        sql_query = "INSERT INTO penjualan (tgl_transaksi, total_harga, jumlah_item, total_bayar, kembalian, status_penjualan, metode_pembayaran) VALUES (%s, %s, %s, %s, %s, 'selesai', %s)"
        cursor.execute(sql_query, (tgl_transaksi, data.get('total_amount', 0), len(cart_items), data.get('paid_amount', 0), data.get('change_amount', 0), data.get('payment_method', 'Tunai')))
        sale_id = cursor.lastrowid
        for item in cart_items:
            insert_detail_query = "INSERT INTO detail_penjualan (id_penjualan, id_produk, jumlah, harga_jual_satuan, subtotal, catatan) VALUES (%s, %s, %s, %s, %s, %s);"
            cursor.execute(insert_detail_query, (sale_id, item['id_produk'], item['jumlah'], item['harga_satuan'], item['subtotal'], item.get('catatan', '')))
            update_stock_query = "UPDATE produk SET stok_saat_ini = stok_saat_ini - %s WHERE id_produk = %s;"
            cursor.execute(update_stock_query, (item['jumlah'], item['id_produk']))
        conn.commit()
        return jsonify({"success": True, "sale_id": sale_id, "message": "Penjualan offline berhasil dicatat."}), 201
    except (Error, ValueError) as e:
        if conn: conn.rollback()
        app_logger.error(f"Error saat proses penjualan offline: {e}", exc_info=True)
        return jsonify({"success": False, "error": f"Gagal menyimpan penjualan: {e}"}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/order", methods=["POST"])
def create_order():
    conn = None
    order_data = request.json
    if not order_data or 'items' not in order_data:
        return jsonify({"success": False, "error": "Data pesanan tidak valid"}), 400
    try:
        conn = get_db_connection(transactional=True)
        if conn is None: return jsonify({"success": False, "error": "Tidak bisa terhubung ke database"}), 500
        cursor = conn.cursor(dictionary=True)
        item_demands = {}
        for item in order_data['items']:
            prod_id = item['id_produk']
            if prod_id in item_demands:
                item_demands[prod_id] += item['jumlah']
            else:
                item_demands[prod_id] = item['jumlah']
        
        for prod_id, total_demand in item_demands.items():
            cursor.execute("SELECT stok_saat_ini, nama_produk FROM produk WHERE id_produk = %s AND is_active = TRUE FOR UPDATE", (prod_id,))
            stock_result = cursor.fetchone()
            if not stock_result or total_demand > stock_result['stok_saat_ini']:
                conn.rollback()
                product_name = stock_result.get('nama_produk', f'ID-{prod_id}') if stock_result else f'ID-{prod_id}'
                stock_available = stock_result.get('stok_saat_ini', 0) if stock_result else 0
                return jsonify({'success': False, 'error': f"Stok tidak mencukupi untuk '{product_name}'. Sisa: {stock_available}, diminta: {total_demand}."}), 400
        
        order_id = str(uuid.uuid4())[:8].upper()
        total_amount = sum(item.get('subtotal', 0) for item in order_data.get('items', []))
        order_data_to_store = {'items': order_data['items'], 'total_amount': total_amount, 'paid_amount': order_data.get('paid_amount', 0), 'change_amount': order_data.get('change_amount', 0), 'payment_method': order_data.get('payment_method')}
        status_penjualan = order_data.get('status_penjualan', 'pending')
        created_at_time = order_data.get('created_at', datetime.now(WIB).strftime('%Y-%m-%d %H:%M:%S'))
        sql_query = "INSERT INTO online_orders (order_id, order_data, status_penjualan, created_at, customer_name, nomor_meja, payment_method) VALUES (%s, %s, %s, %s, %s, %s, %s)"
        cursor.execute(sql_query, (order_id, json.dumps(order_data_to_store), status_penjualan, created_at_time, order_data.get('customer_name', 'N/A'), order_data.get('table_number'), order_data.get('payment_method')))
        conn.commit()
        return jsonify({"success": True, "order_id": order_id, "message": "Pesanan berhasil dibuat."}), 201
    except (Exception, Error) as e:
        if conn: conn.rollback()
        app_logger.error(f"Error saat membuat pesanan baru: {e}", exc_info=True)
        return jsonify({"success": False, "error": f"Gagal menyimpan pesanan: {e}"}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/orders", methods=["GET"])
def get_orders_by_status():
    conn = None
    status = request.args.get('status')
    if not status: return jsonify({"success": False, "error": "Parameter 'status' wajib diisi"}), 400
    try:
        conn = get_db_connection()
        if conn is None: return jsonify({"success": False, "error": "Tidak bisa terhubung ke database"}), 500
        cursor = conn.cursor(dictionary=True)
        sql_query = "SELECT * FROM online_orders WHERE status_penjualan = %s ORDER BY created_at ASC"
        cursor.execute(sql_query, (status,))
        orders_list = []
        for row in cursor.fetchall():
            order_data = json.loads(row['order_data'])
            orders_list.append({"order_code": row['order_id'], "status_penjualan": row['status_penjualan'], "created_at": row['created_at'].isoformat(), "updated_at": row['updated_at'].isoformat() if row['updated_at'] else row['created_at'].isoformat(), "customer_name": row.get('customer_name', 'N/A'), "nomor_meja": row.get('nomor_meja'), "payment_method": row.get('payment_method', 'N/A'), "items": order_data.get('items', [])})
        return jsonify({"success": True, "data": orders_list}), 200
    except (Exception, Error) as e:
        app_logger.error(f"Error saat mengambil pesanan berdasarkan status '{status}': {e}", exc_info=True)
        return jsonify({"success": False, "error": "Gagal mengambil pesanan"}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/order/<order_id>", methods=["GET"])
def get_order_by_id(order_id):
    conn = None
    try:
        conn = get_db_connection()
        if conn is None: return jsonify({"success": False, "error": "Tidak bisa terhubung ke database"}), 500
        cursor = conn.cursor(dictionary=True)
        sql_query = "SELECT * FROM online_orders WHERE order_id = %s"
        cursor.execute(sql_query, (order_id.upper(),))
        record = cursor.fetchone()
        if not record:
            return jsonify({"success": False, "error": "Pesanan tidak ditemukan"}), 404
        order_data = json.loads(record['order_data'])
        order = {**record, "created_at": record['created_at'].isoformat(), **order_data}
        del order['order_data']
        return jsonify({"success": True, "data": order}), 200
    except (Exception, Error) as e:
        app_logger.error(f"Error saat mengambil pesanan by ID '{order_id}': {e}", exc_info=True)
        return jsonify({"success": False, "error": "Gagal mengambil pesanan"}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route("/api/order/<order_id>/status", methods=["PUT"])
def update_order_status_route(order_id):
    conn = None
    data = request.json
    status = data.get('status')
    if not status: 
        return jsonify({"success": False, "error": "Parameter 'status' wajib diisi"}), 400
        
    try:
        conn = get_db_connection(transactional=True)
        if conn is None: 
            return jsonify({"success": False, "error": "Tidak bisa terhubung ke database"}), 500
            
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT order_data, status_penjualan FROM online_orders WHERE order_id = %s FOR UPDATE", (order_id.upper(),))
        order_record = cursor.fetchone()
        
        if not order_record:
            conn.rollback()
            return jsonify({"success": False, "error": "Pesanan tidak ditemukan."}), 404
            
        order_data_from_db = json.loads(order_record['order_data'])
        existing_status = order_record['status_penjualan']
        items_to_process = data.get('items', order_data_from_db.get('items', []))
        
        if not items_to_process:
            conn.rollback()
            return jsonify({"success": False, "error": "Tidak ada item dalam pesanan untuk diproses."}), 400
        
        if status == 'completed':
            if existing_status == 'completed': 
                conn.rollback()
                return jsonify({"success": False, "error": "Pesanan ini sudah selesai."}), 400
            
            item_demands = {}
            for item in items_to_process:
                prod_id = item['id_produk']
                if prod_id in item_demands:
                    item_demands[prod_id] += item['jumlah']
                else:
                    item_demands[prod_id] = item['jumlah']
            
            for prod_id, total_demand in item_demands.items():
                cursor.execute("SELECT nama_produk, stok_saat_ini FROM produk WHERE id_produk = %s FOR UPDATE", (prod_id,))
                product = cursor.fetchone()
                
                product_name = product['nama_produk'] if product else f"Produk ID:{prod_id}"

                if not product:
                    conn.rollback()
                    return jsonify({"success": False, "error": f"Produk '{product_name}' tidak ditemukan di database."}), 404
                
                if total_demand > product['stok_saat_ini']:
                    conn.rollback()
                    return jsonify({"success": False, "error": "Stok tidak mencukupi", "product_name": product_name}), 400

            tgl_transaksi = data.get('tgl_transaksi', datetime.now(WIB).strftime('%Y-%m-%d %H:%M:%S'))
            sale_query = "INSERT INTO penjualan (tgl_transaksi, total_harga, jumlah_item, total_bayar, kembalian, metode_pembayaran, status_penjualan) VALUES (%s, %s, %s, %s, %s, %s, 'selesai')"
            total_amount = sum(item['subtotal'] for item in items_to_process)
            cursor.execute(sale_query, (tgl_transaksi, total_amount, len(items_to_process), data.get('paid_amount', 0), data.get('change_amount', 0), data.get('payment_method', 'Tunai')))
            sale_id = cursor.lastrowid
            
            for item in items_to_process:
                detail_query = "INSERT INTO detail_penjualan (id_penjualan, id_produk, jumlah, harga_jual_satuan, subtotal, catatan) VALUES (%s, %s, %s, %s, %s, %s);"
                cursor.execute(detail_query, (sale_id, item['id_produk'], item['jumlah'], item['harga_satuan'], item['subtotal'], item.get('catatan', '')))
                stock_query = "UPDATE produk SET stok_saat_ini = stok_saat_ini - %s WHERE id_produk = %s;"
                cursor.execute(stock_query, (item['jumlah'], item['id_produk']))
            
            order_data_from_db['items'] = items_to_process
            order_data_from_db.update({'paid_amount': data.get('paid_amount',0), 'change_amount': data.get('change_amount',0), 'payment_method': data.get('payment_method','Tunai'), 'total_amount': total_amount})
            update_order_query = "UPDATE online_orders SET status_penjualan = 'completed', updated_at = NOW(), order_data = %s WHERE order_id = %s;"
            cursor.execute(update_order_query, (json.dumps(order_data_from_db), order_id.upper()))
            
            conn.commit()
            return jsonify({"success": True, "message": "Pesanan selesai, penjualan dicatat, dan stok diperbarui.", "sale_id": sale_id}), 200

        elif status == 'in_progress':
            order_data_from_db['items'] = items_to_process
            order_data_from_db.update({'paid_amount': data.get('paid_amount'), 'change_amount': data.get('change_amount'), 'payment_method': data.get('payment_method')})
            sql_query = "UPDATE online_orders SET status_penjualan = %s, updated_at = NOW(), order_data = %s WHERE order_id = %s"
            cursor.execute(sql_query, (status, json.dumps(order_data_from_db), order_id.upper()))
            conn.commit()
            return jsonify({"success": True, "message": "Status berhasil diperbarui"}), 200
            
        elif status == 'cancelled':
            if existing_status in ['completed', 'cancelled']:
                conn.rollback()
                return jsonify({"success": False, "error": f"Pesanan '{existing_status}' tidak dapat dibatalkan."}), 400
            update_order_query = "UPDATE online_orders SET status_penjualan = 'cancelled', updated_at = NOW() WHERE order_id = %s;"
            cursor.execute(update_order_query, (order_id.upper(),))
            conn.commit()
            return jsonify({"success": True, "message": "Pesanan berhasil dibatalkan."}), 200
        else:
            conn.rollback()
            return jsonify({"success": False, "error": "Status tidak valid."}), 400

    except (Error, ValueError) as e:
        if conn: conn.rollback()
        app_logger.error(f"Error saat update status pesanan ID {order_id}: {e}", exc_info=True)
        return jsonify({"success": False, "error": f"Gagal memperbarui status: {e}"}), 500
    finally:
        if conn and conn.is_connected(): conn.close()

@app.route('/product_images/<path:filename>')
def serve_product_image(filename):
    """Menyajikan file gambar produk dari direktori yang ditentukan."""
    return send_from_directory(IMAGE_FOLDER, filename)

if __name__ == "__main__":
    app_logger.info("================== SERVER API DIMULAI ==================")
    app.run(host="0.0.0.0", port=5000, debug=False)