<?php
// ============================================================
//  MJPOS Mobile API Bridge  –  v1.0
//  Place this file at:  /public/mobile-api.php
//  Access via:          http://localhost:8080/mjpos/public/mobile-api.php
// ============================================================

// ── Pull in MJPOS config ─────────────────────────────────────
$configFile = dirname(__DIR__) . '/config/config.php';
if (file_exists($configFile)) {
    require_once $configFile;
} else {
    // Fallback – hardcode if config path differs
    define('DB_HOST',    'localhost');
    define('DB_NAME',    'mjpos_db');
    define('DB_USER',    'root');
    define('DB_PASS',    '');
    define('DB_CHARSET', 'utf8mb4');
    define('TIMEZONE',   'Asia/Colombo');
    date_default_timezone_set('Asia/Colombo');
}

// ── CORS & JSON headers ──────────────────────────────────────
header('Content-Type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, X-Requested-With');
header('Cache-Control: no-cache, no-store, must-revalidate');
header('X-Powered-By: MJPOS-MobileAPI/1.0');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') { http_response_code(204); exit; }

// ── Connect ──────────────────────────────────────────────────
function getDB(): PDO {
    static $pdo = null;
    if ($pdo) return $pdo;
    $dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=' . DB_CHARSET;
    $pdo = new PDO($dsn, DB_USER, DB_PASS, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
    return $pdo;
}

function ok(array $data): void  { echo json_encode(['ok' => true, 'ts' => date('c'), 'data' => $data]); exit; }
function err(string $msg, int $code = 500): void {
    http_response_code($code);
    echo json_encode(['ok' => false, 'error' => $msg]);
    exit;
}

// ── Routing ──────────────────────────────────────────────────
$action = trim($_GET['action'] ?? 'dashboard');

try {
    $db = getDB();

    switch ($action) {

        // ── DASHBOARD ─────────────────────────────────────────
        case 'dashboard':

            // ── TODAY ──────────────────────────────────────────
            $today = $db->query("
                SELECT
                  COUNT(*)                                                              AS invoices,
                  COALESCE(SUM(total),0)                                               AS revenue,
                  COALESCE(SUM(paid),0)                                                AS paid_total,
                  COALESCE(SUM(balance),0)                                             AS balance_total,
                  COALESCE(SUM(CASE WHEN payment_status='paid'    THEN total ELSE 0 END),0) AS paid_sales,
                  COALESCE(SUM(CASE WHEN payment_status='due'     THEN total ELSE 0 END),0) AS due_sales,
                  COALESCE(SUM(CASE WHEN payment_status='partial' THEN total ELSE 0 END),0) AS partial_sales,
                  COALESCE(SUM(discount),0)                                            AS total_discount,
                  COALESCE(MAX(total),0)                                               AS largest_sale
                FROM sales
                WHERE DATE(sale_date) = CURDATE() AND status != 'void'
            ")->fetch();

            // Today returns
            $todayReturns = $db->query("
                SELECT COALESCE(SUM(total),0) AS total, COUNT(*) AS cnt
                FROM sales_returns WHERE DATE(return_date) = CURDATE()
            ")->fetch();

            // Today expenses
            $todayExp = $db->query("
                SELECT COALESCE(SUM(amount),0) FROM expenses WHERE DATE(expense_date) = CURDATE()
            ")->fetchColumn();

            // Yesterday for comparison
            $yesterday = $db->query("
                SELECT COALESCE(SUM(total),0) AS revenue, COUNT(*) AS invoices
                FROM sales WHERE DATE(sale_date) = DATE_SUB(CURDATE(),INTERVAL 1 DAY) AND status!='void'
            ")->fetch();

            // Today's hourly breakdown
            $hourly = $db->query("
                SELECT HOUR(created_at) AS hr,
                       COUNT(*)          AS cnt,
                       COALESCE(SUM(total),0) AS rev
                FROM   sales
                WHERE  DATE(sale_date) = CURDATE() AND status != 'void'
                GROUP  BY HOUR(created_at)
                ORDER  BY hr ASC
            ")->fetchAll();

            // Today's sales list (latest 20)
            $todaySales = $db->query("
                SELECT s.invoice_no, s.created_at, s.total, s.paid,
                       s.balance, s.payment_status, s.status, s.discount,
                       COALESCE(c.customer_name,'Walk-in') AS customer
                FROM   sales s
                LEFT   JOIN customers c ON c.id = s.customer_id
                WHERE  DATE(s.sale_date) = CURDATE() AND s.status != 'void'
                ORDER  BY s.created_at DESC
                LIMIT  20
            ")->fetchAll();

            // ── ALL-TIME KPIs ──────────────────────────────────
            $allTime = $db->query("
                SELECT
                  COUNT(*)                 AS total_invoices,
                  COALESCE(SUM(total),0)   AS gross_sales,
                  COALESCE(SUM(CASE WHEN payment_status='due'     THEN balance ELSE 0 END),0) AS total_due,
                  COALESCE(SUM(CASE WHEN payment_status='partial' THEN balance ELSE 0 END),0) AS total_partial
                FROM sales WHERE status != 'void'
            ")->fetch();

            $allReturns   = $db->query("SELECT COALESCE(SUM(total),0) FROM sales_returns")->fetchColumn();
            $allPurchases = $db->query("SELECT COALESCE(SUM(total),0) FROM purchases")->fetchColumn();
            $allExpenses  = $db->query("SELECT COALESCE(SUM(amount),0) FROM expenses")->fetchColumn();

            // Daily sales last 14 days
            $daily = $db->query("
                SELECT DATE(sale_date) AS day, COUNT(*) AS invoices, SUM(total) AS revenue
                FROM   sales WHERE status != 'void'
                  AND  sale_date >= DATE_SUB(CURDATE(), INTERVAL 13 DAY)
                GROUP  BY DATE(sale_date) ORDER BY day ASC
            ")->fetchAll();

            // Daily returns last 14 days
            $dailyRet = $db->query("
                SELECT return_date AS day, COALESCE(SUM(total),0) AS returns
                FROM   sales_returns
                WHERE  return_date >= DATE_SUB(CURDATE(), INTERVAL 13 DAY)
                GROUP  BY return_date ORDER BY day ASC
            ")->fetchAll();

            // Outstanding customers
            $outstanding = $db->query("
                SELECT c.customer_name, c.phone, c.current_balance, c.credit_limit,
                       (SELECT s.invoice_no FROM sales s
                        WHERE s.customer_id=c.id AND s.payment_status IN('due','partial')
                        ORDER BY s.sale_date DESC LIMIT 1) AS last_inv
                FROM   customers c
                WHERE  c.current_balance > 0
                ORDER  BY c.current_balance DESC
            ")->fetchAll();

            // Low stock
            $lowStock = $db->query("
                SELECT COUNT(*) FROM products
                WHERE status='active' AND reorder_level>0 AND current_stock <= reorder_level
            ")->fetchColumn();

            ok([
                'today'        => array_merge($today, ['returns'=>(float)$todayReturns['total'], 'returns_cnt'=>(int)$todayReturns['cnt'], 'expenses'=>(float)$todayExp]),
                'yesterday'    => $yesterday,
                'hourly'       => $hourly,
                'today_sales'  => $todaySales,
                'all_time'     => array_merge($allTime, ['returns'=>(float)$allReturns, 'purchases'=>(float)$allPurchases, 'expenses'=>(float)$allExpenses]),
                'low_stock'    => (int)$lowStock,
                'daily'        => $daily,
                'daily_ret'    => $dailyRet,
                'outstanding'  => $outstanding,
                'server_date'  => date('Y-m-d'),
                'server_time'  => date('H:i:s'),
            ]);

        // ── SALES ─────────────────────────────────────────────
        case 'sales':
            $status  = $_GET['status']  ?? '';
            $search  = '%'.trim($_GET['q'] ?? '').'%';
            $limit   = max(1, min(200, (int)($_GET['limit'] ?? 100)));
            $offset  = max(0, (int)($_GET['offset'] ?? 0));

            $where = "WHERE 1=1";
            $params = [];
            if ($status && $status !== 'all') {
                if ($status === 'returned') { $where .= " AND s.status='returned'"; }
                else { $where .= " AND s.payment_status=?"; $params[] = $status; }
            }
            if (trim($_GET['q'] ?? '') !== '') {
                $where .= " AND (s.invoice_no LIKE ? OR c.customer_name LIKE ? OR s.note LIKE ?)";
                $params = array_merge($params, [$search, $search, $search]);
            }

            $stmt = $db->prepare("
                SELECT s.id, s.invoice_no, s.sale_date, s.subtotal, s.discount, s.tax, s.total,
                       s.paid, s.balance, s.payment_status, s.status, s.note,
                       COALESCE(c.customer_name,'Walk-in') AS customer, c.phone
                FROM   sales s
                LEFT   JOIN customers c ON c.id=s.customer_id
                $where ORDER BY s.sale_date DESC, s.id DESC
                LIMIT ? OFFSET ?
            ");
            $params[] = $limit; $params[] = $offset;
            $stmt->execute($params);
            $rows = $stmt->fetchAll();

            $cntStmt = $db->prepare("SELECT COUNT(*) FROM sales s LEFT JOIN customers c ON c.id=s.customer_id $where");
            $cntStmt->execute(array_slice($params, 0, -2));
            $total = $cntStmt->fetchColumn();

            ok(['sales' => $rows, 'total' => (int)$total, 'limit' => $limit, 'offset' => $offset]);

        // ── PRODUCTS ──────────────────────────────────────────
        case 'products':
            $cat    = trim($_GET['cat'] ?? '');
            $search = '%'.trim($_GET['q'] ?? '').'%';
            $low    = (int)($_GET['low'] ?? 0);

            $where  = "WHERE p.status='active'";
            $params = [];
            if ($cat)  { $where .= " AND cat.category_name=?"; $params[] = $cat; }
            if ($low)  { $where .= " AND p.reorder_level>0 AND p.current_stock<=p.reorder_level"; }
            if (trim($_GET['q'] ?? '') !== '') {
                $where .= " AND (p.product_name LIKE ? OR p.product_code LIKE ? OR p.barcode LIKE ?)";
                $params = array_merge($params, [$search, $search, $search]);
            }

            $stmt = $db->prepare("
                SELECT p.id, p.product_code, p.product_name, p.barcode,
                       p.cost_price, p.selling_price, p.wholesale_price,
                       p.current_stock, p.reorder_level, p.status,
                       p.tax_percent, p.discount_percent,
                       cat.category_name AS category,
                       b.brand_name      AS brand,
                       u.unit_name       AS unit
                FROM   products p
                LEFT   JOIN categories cat ON cat.id=p.category_id
                LEFT   JOIN brands      b   ON b.id=p.brand_id
                LEFT   JOIN units       u   ON u.id=p.unit_id
                $where
                ORDER  BY cat.category_name, p.product_name
            ");
            $stmt->execute($params);
            $rows = $stmt->fetchAll();

            // Summary
            $summary = $db->query("
                SELECT COUNT(*) AS total,
                  SUM(CASE WHEN reorder_level>0 AND current_stock<=reorder_level THEN 1 ELSE 0 END) AS low,
                  SUM(CASE WHEN reorder_level>0 AND current_stock<=reorder_level*1.5+3 AND current_stock>reorder_level THEN 1 ELSE 0 END) AS watch,
                  COALESCE(SUM(current_stock*cost_price),0) AS stock_value
                FROM products WHERE status='active'
            ")->fetch();

            // Categories list
            $cats = $db->query("
                SELECT cat.category_name, COUNT(p.id) AS count
                FROM   products p
                JOIN   categories cat ON cat.id=p.category_id
                WHERE  p.status='active'
                GROUP  BY cat.category_name
                ORDER  BY count DESC
            ")->fetchAll();

            ok(['products' => $rows, 'summary' => $summary, 'categories' => $cats]);

        // ── FINANCE ───────────────────────────────────────────
        case 'finance':
            // Sales summary
            $salesSum = $db->query("
                SELECT SUM(total) AS gross, SUM(paid) AS paid_total,
                       SUM(balance) AS balance_total, COUNT(*) AS cnt
                FROM sales WHERE status!='void'
            ")->fetch();
            $retTotal = $db->query("SELECT COALESCE(SUM(total),0) FROM sales_returns")->fetchColumn();

            // Purchases
            $purchases = $db->query("
                SELECT pu.invoice_no, pu.purchase_date, pu.total, pu.paid,
                       pu.balance, pu.payment_status, pu.status,
                       COALESCE(s.supplier_name,'—') AS supplier
                FROM   purchases pu
                LEFT   JOIN suppliers s ON s.id=pu.supplier_id
                ORDER  BY pu.purchase_date DESC, pu.id DESC
            ")->fetchAll();

            $purSum = $db->query("SELECT COALESCE(SUM(total),0) FROM purchases")->fetchColumn();

            // Expenses
            $expenses = $db->query("
                SELECT e.id, e.title, e.amount, e.expense_date, e.reference, e.note,
                       ec.category_name
                FROM   expenses e
                LEFT   JOIN expense_categories ec ON ec.id=e.expense_category_id
                ORDER  BY e.expense_date DESC
            ")->fetchAll();
            $expSum = $db->query("SELECT COALESCE(SUM(amount),0) FROM expenses")->fetchColumn();

            // Customer balances
            $customers = $db->query("
                SELECT c.customer_name, c.phone, c.address, c.current_balance,
                       c.credit_limit, c.opening_balance, c.loyalty_points,
                       COUNT(s.id) AS total_orders,
                       COALESCE(SUM(s.total),0) AS lifetime_value
                FROM   customers c
                LEFT   JOIN sales s ON s.customer_id=c.id AND s.status!='void'
                GROUP  BY c.id
                ORDER  BY c.current_balance DESC, lifetime_value DESC
            ")->fetchAll();

            // Supplier balances
            $suppliers = $db->query("
                SELECT s.supplier_name, s.phone, s.address, s.current_balance,
                       COUNT(p.id) AS orders,
                       COALESCE(SUM(p.total),0) AS total_purchased
                FROM   suppliers s
                LEFT   JOIN purchases p ON p.supplier_id=s.id
                GROUP  BY s.id
                ORDER  BY total_purchased DESC
            ")->fetchAll();

            // Monthly sales (last 6 months)
            $monthly = $db->query("
                SELECT DATE_FORMAT(sale_date,'%Y-%m') AS month,
                       COUNT(*) AS invoices, SUM(total) AS revenue
                FROM   sales WHERE status!='void'
                  AND  sale_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
                GROUP  BY DATE_FORMAT(sale_date,'%Y-%m')
                ORDER  BY month ASC
            ")->fetchAll();

            ok([
                'sales_summary'  => array_merge($salesSum, ['returns'=>(float)$retTotal]),
                'purchases'      => $purchases,
                'purchases_total'=> (float)$purSum,
                'expenses'       => $expenses,
                'expenses_total' => (float)$expSum,
                'customers'      => $customers,
                'suppliers'      => $suppliers,
                'monthly'        => $monthly,
            ]);

        // ── SETTINGS ──────────────────────────────────────────
        case 'settings':
            $rows = $db->query("
                SELECT setting_key, setting_value, updated_at
                FROM settings ORDER BY id ASC
            ")->fetchAll();
            $map = [];
            foreach ($rows as $r) $map[$r['setting_key']] = $r;
            ok(['settings' => $map]);

        // ── TEAM ──────────────────────────────────────────────
        case 'team':
            $users = $db->query("
                SELECT u.id, u.name, u.username, u.email, u.phone,
                       u.status, u.last_login, u.last_active, u.created_at,
                       r.role_name
                FROM   users u
                LEFT   JOIN roles r ON r.id=u.role_id
                ORDER  BY u.role_id ASC, u.name ASC
            ")->fetchAll();

            // ── Activity per user (safe – audit_logs schema may vary) ──
            $activity = [];
            try {
                // Detect available action column name
                $cols = $db->query("SHOW COLUMNS FROM audit_logs")->fetchAll(PDO::FETCH_COLUMN);
                $actionCol = in_array('action', $cols)
                    ? 'action'
                    : (in_array('action_type', $cols) ? 'action_type' : null);

                if ($actionCol) {
                    $actRows = $db->query("
                        SELECT user_id,
                               COUNT(*)  AS total_actions,
                               SUM(CASE WHEN {$actionCol} LIKE 'sale%'     THEN 1 ELSE 0 END) AS sales_actions,
                               SUM(CASE WHEN {$actionCol} LIKE 'purchase%' THEN 1 ELSE 0 END) AS purchase_actions,
                               SUM(CASE WHEN {$actionCol} IN('login','user_login') THEN 1 ELSE 0 END) AS logins,
                               MAX(created_at) AS last_action_at
                        FROM   audit_logs
                        WHERE  created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                          AND  user_id IS NOT NULL
                        GROUP  BY user_id
                    ")->fetchAll(PDO::FETCH_ASSOC);   // ← FETCH_ASSOC not FETCH_KEY_PAIR
                } else {
                    // Fallback: just count per user
                    $actRows = $db->query("
                        SELECT user_id,
                               COUNT(*) AS total_actions,
                               0 AS sales_actions, 0 AS purchase_actions,
                               0 AS logins, MAX(created_at) AS last_action_at
                        FROM   audit_logs
                        WHERE  created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                          AND  user_id IS NOT NULL
                        GROUP  BY user_id
                    ")->fetchAll(PDO::FETCH_ASSOC);
                }

                // Re-index by user_id so JS can do activity[user.id]
                foreach ($actRows as $row) {
                    $activity[(string)$row['user_id']] = $row;
                }
            } catch (Throwable $e) {
                // audit_logs unavailable – continue without activity data
                $activity = [];
            }

            // ── Overall stats (safe) ───────────────────────────
            $stats = [
                'total_audit'    => 0,
                'failed_logins'  => 0,
                'total_sales'    => 0,
                'total_returns'  => 0,
                'total_purchases'=> 0,
            ];
            try {
                $stats['total_sales']     = (int)$db->query("SELECT COUNT(*) FROM sales")->fetchColumn();
                $stats['total_returns']   = (int)$db->query("SELECT COUNT(*) FROM sales_returns")->fetchColumn();
                $stats['total_purchases'] = (int)$db->query("SELECT COUNT(*) FROM purchases")->fetchColumn();
            } catch (Throwable $e) {}

            try {
                $stats['total_audit'] = (int)$db->query("SELECT COUNT(*) FROM audit_logs")->fetchColumn();
            } catch (Throwable $e) {}

            try {
                // Try common column names for failed-login action
                $fl = $db->query("
                    SELECT COUNT(*) FROM audit_logs
                    WHERE action IN('login_failed','failed_login','auth_failed')
                       OR (action='login' AND description LIKE '%fail%')
                ")->fetchColumn();
                $stats['failed_logins'] = (int)$fl;
            } catch (Throwable $e) {}

            ok(['users' => $users, 'activity' => $activity, 'stats' => $stats]);

        // ── STOCK MOVEMENTS ───────────────────────────────────
        case 'stock_movements':
            $product = (int)($_GET['product_id'] ?? 0);
            $limit   = max(1, min(50, (int)($_GET['limit'] ?? 20)));
            $where   = $product ? "WHERE sm.product_id=$product" : "";
            $rows = $db->query("
                SELECT sm.id, sm.movement_type, sm.quantity_in, sm.quantity_out,
                       sm.stock_after, sm.reference_no, sm.note, sm.created_at,
                       p.product_name, p.product_code,
                       COALESCE(u.name,'System') AS created_by
                FROM   stock_movements sm
                JOIN   products p ON p.id=sm.product_id
                LEFT   JOIN users u ON u.id=sm.created_by
                $where
                ORDER  BY sm.created_at DESC LIMIT $limit
            ")->fetchAll();
            ok(['movements' => $rows]);

        // ── HEALTH CHECK ──────────────────────────────────────
        case 'ping':
            $dbOk = false;
            try { $db->query('SELECT 1'); $dbOk = true; } catch (Exception $e) {}

            // Pull shop identity from settings table
            $shopSettings = [];
            try {
                $rows = $db->query("
                    SELECT setting_key, setting_value
                    FROM   settings
                    WHERE  setting_key IN (
                        'shop_name','shop_address','shop_phone',
                        'shop_email','currency_symbol','currency_code','timezone'
                    )
                ")->fetchAll(PDO::FETCH_KEY_PAIR);
                $shopSettings = $rows;
            } catch (Throwable $e) {}

            ok([
                'shop_name'      => $shopSettings['shop_name']      ?? (defined('APP_NAME') ? APP_NAME : 'MJPOS'),
                'shop_address'   => $shopSettings['shop_address']    ?? '',
                'shop_phone'     => $shopSettings['shop_phone']      ?? '',
                'shop_email'     => $shopSettings['shop_email']      ?? '',
                'currency'       => $shopSettings['currency_symbol'] ?? 'Rs.',
                'currency_code'  => $shopSettings['currency_code']   ?? 'LKR',
                'app'            => defined('APP_NAME')    ? APP_NAME    : 'MJPOS',
                'version'        => defined('APP_VERSION') ? APP_VERSION : '—',
                'env'            => defined('APP_ENV')     ? APP_ENV     : 'unknown',
                'db'             => $dbOk ? 'connected' : 'error',
                'timezone'       => $shopSettings['timezone'] ?? date_default_timezone_get(),
                'server_time'    => date('Y-m-d H:i:s'),
            ]);

        default:
            err("Unknown action: $action", 400);
    }

} catch (PDOException $e) {
    err('Database error: ' . $e->getMessage());
} catch (Throwable $e) {
    err('Server error: ' . $e->getMessage());
}