<?php
namespace App\Core;

use Throwable;

class ModuleCrud {
    public static function allowedColumns(string $table): array {
        $map = [
            'products'=>['vendor_id','category_id','brand_id','name','slug','sku','barcode','product_code','product_type','purchase_price','regular_price','sale_price','flash_price','stock','weight','image','video_url','short_description','description','reward_points','shipping_charge','is_featured','is_trending','is_best_selling','status','meta_title','meta_description','meta_keywords','og_image'],
            'categories'=>['parent_id','name','slug','description','banner','meta_title','meta_description','status','sort_order'],
            'brands'=>['name','slug','logo','description','meta_title','meta_description','status'],
            'vendors'=>['user_id','shop_name','slug','logo','banner','address','policies','commission_type','commission_value','rating','status'],
            'users'=>['role','name','email','phone','avatar','status','two_factor_enabled'],
            'orders'=>['order_no','customer_id','customer_name','customer_email','customer_phone','subtotal','discount_amount','reward_discount','shipping_charge','vat_amount','total_amount','paid_amount','due_amount','payment_method','payment_status','order_status','delivery_address','order_note','tracking_no'],
            'commission_rules'=>['name','rule_type','vendor_id','category_id','product_id','commission_value','status'],
            'settings'=>['setting_key','setting_value','setting_group'],
            'wallets'=>['user_id','wallet_type','balance'],
            'withdraw_requests'=>['user_id','wallet_type','amount','fee','net_amount','method','account_details','status'],
            'payment_gateways'=>['code','name','is_enabled','sandbox_mode','config_json','sort_order'],
            'delivery_zones'=>['name','country_id','district_ids','area_ids','base_charge','extra_kg_charge','free_delivery_min','status'],
            'shipping_rules'=>['rule_name','rule_type','vendor_id','product_id','category_id','district_id','area_id','min_order_amount','charge','is_free','status'],
            'affiliate_members'=>['user_id','sponsor_id','referral_code','rank_code','total_psa','weekly_personal_sales','landing_title','landing_bio','landing_banner','landing_image','status'],
            'affiliate_ranks'=>['code','name','required_psa','royalty_eligible','sort_order'],
            'affiliate_plans'=>['code','name','price','product_limit','affiliate_percent','leader_percent','mentor_percent','cm_percent','status'],
            'psa_rules'=>['rule_code','rule_name','product_mode','marketing_percent','management_percent','affiliate_distribution_percent','global_royalty_percent','leadership_royalty_percent','incentive_percent','status'],
            'coupons'=>['code','discount_type','discount_value','min_order_amount','starts_at','ends_at','usage_limit','status'],
            'pages'=>['page_category_id','title','slug','content','meta_title','meta_description','status'],
            'sliders'=>['title','subtitle','image','link','sort_order','status'],
            'banners'=>['title','image','link','position','status'],
            'menus'=>['title','url','parent_id','location','sort_order','status'],
            'notifications'=>['user_id','channel','title','message','read_at'],
            'roles'=>['code','name','description'],
            'pos_outlets'=>['vendor_id','name','code','address','phone','status'],
            'pos_registers'=>['vendor_id','outlet_id','name','register_code','device_uid','receipt_width','printer_type','cash_drawer_enabled','allow_offline','status'],
            'pos_roles'=>['code','name','description'],
            'pos_role_permissions'=>['pos_role_id','ability','description'],
            'pos_staff_assignments'=>['vendor_id','user_id','pos_role_id','outlet_id','register_id','status'],
            'pos_devices'=>['vendor_id','register_id','device_uid','device_name','device_type','browser_info','last_ip','status'],
            'pos_shifts'=>['register_id','cashier_id','opening_cash','closing_cash','expected_cash','variance','status','opened_at','closed_at'],
            'pos_cash_movements'=>['shift_id','movement_type','amount','reason','created_by'],
            'pos_held_sales'=>['hold_no','vendor_id','register_id','shift_id','cashier_id','customer_name','cart_json','total_amount','status'],
            'pos_sales'=>['sale_no','vendor_id','register_id','shift_id','cashier_id','customer_id','subtotal','discount_amount','tax_amount','total_amount','paid_amount','change_amount','payment_status','sale_status','note'],
            'pos_sale_items'=>['sale_id','product_id','variant_id','sku','barcode','product_name','qty','unit_price','discount_amount','line_total'],
            'pos_payments'=>['sale_id','payment_method','amount','reference_no','provider','status'],
            'pos_refunds'=>['sale_id','refund_no','amount','method','reason','status','created_by'],
            'pos_receipt_templates'=>['vendor_id','name','width','header_html','footer_html','show_logo','show_barcode','is_default','status'],
            'pos_scanner_events'=>['vendor_id','register_id','cashier_id','scan_value','scan_type','matched_product_id','status'],
            'pos_stock_movements'=>['sale_id','product_id','variant_id','vendor_id','movement_type','qty','note','created_by'],
            'pos_sync_queue'=>['client_uuid','user_id','payload_json','status','response_json'],
            'pos_settings'=>['vendor_id','setting_key','setting_value','setting_group'],
            'pos_tax_rules'=>['vendor_id','name','tax_percent','applies_to','status'],
            'pos_discount_rules'=>['vendor_id','code','name','discount_type','discount_value','starts_at','ends_at','status'],
            'pos_day_closings'=>['vendor_id','outlet_id','business_date','gross_sales','discount_amount','tax_amount','refund_amount','net_sales','closed_by','status'],
        ];
        $configured = $map[$table] ?? [];
        $actual = self::tableColumns($table);
        if ($actual) {
            $blocked = ['id','password_hash','transaction_pin_hash','client_secret','token_hash','otp_code','auth_token','public_key','created_at','updated_at'];
            $actual = array_values(array_diff($actual, $blocked));
            return $configured ? array_values(array_intersect($configured, $actual)) : array_slice($actual, 0, 60);
        }
        return $configured ?: ['name','title','code','description','status','sort_order'];
    }

    public static function tableColumns(string $table): array {
        if (!preg_match('/^[a-z0-9_]+$/', $table) || !Database::tableExists($table)) return [];
        static $cache = [];
        return $cache[$table] ??= Database::columns($table);
    }


    public static function hasId(string $table): bool {
        return in_array('id', self::tableColumns($table), true);
    }

    public static function searchableColumns(string $table): array {
        $columns = self::allowedColumns($table);
        $preferred = ['name','title','shop_name','code','slug','email','phone','order_no','invoice_no','sale_no','sku','barcode','product_code','subject','message','description','status'];
        $searchable = array_values(array_intersect($preferred, $columns));
        if (!$searchable) $searchable = array_slice($columns, 0, 5);
        return $searchable;
    }

    public static function listFiltered(string $table, string $query = '', int $limit = 25, int $offset = 0): array {
        if (!preg_match('/^[a-z0-9_]+$/', $table) || !Database::tableExists($table)) return self::sampleRows($table);
        $limit = max(1, min(100, $limit));
        $offset = max(0, $offset);
        $where = '';
        $params = [];
        $query = trim($query);
        if ($query !== '') {
            $parts = [];
            foreach (self::searchableColumns($table) as $column) {
                if (!in_array($column, self::tableColumns($table), true)) continue;
                $parts[] = "CAST(`{$column}` AS CHAR) LIKE ?";
                $params[] = '%' . $query . '%';
            }
            if ($parts) $where = ' WHERE ' . implode(' OR ', $parts);
        }
        $order = self::hasId($table) ? ' ORDER BY id DESC' : '';
        return Database::safeFetchAll("SELECT * FROM `{$table}`{$where}{$order} LIMIT {$limit} OFFSET {$offset}", $params);
    }

    public static function countFiltered(string $table, string $query = ''): int {
        if (!preg_match('/^[a-z0-9_]+$/', $table) || !Database::tableExists($table)) return 0;
        $where = '';
        $params = [];
        $query = trim($query);
        if ($query !== '') {
            $parts = [];
            foreach (self::searchableColumns($table) as $column) {
                if (!in_array($column, self::tableColumns($table), true)) continue;
                $parts[] = "CAST(`{$column}` AS CHAR) LIKE ?";
                $params[] = '%' . $query . '%';
            }
            if ($parts) $where = ' WHERE ' . implode(' OR ', $parts);
        }
        $row = Database::safeFetchOne("SELECT COUNT(*) total FROM `{$table}`{$where}", $params);
        return (int)($row['total'] ?? 0);
    }

    public static function list(string $table, int $limit = 20): array {
        if (!preg_match('/^[a-z0-9_]+$/', $table) || !Database::tableExists($table)) return self::sampleRows($table);
        $limit = max(1, min(200, $limit));
        return Database::safeFetchAll("SELECT * FROM `{$table}` ORDER BY id DESC LIMIT {$limit}");
    }

    public static function find(string $table, int $id): ?array {
        if ($id < 1 || !Database::tableExists($table)) return null;
        return Database::safeFetchOne("SELECT * FROM `{$table}` WHERE id=? LIMIT 1", [$id]);
    }

    public static function count(string $table): int {
        if (!preg_match('/^[a-z0-9_]+$/', $table)) return 0;
        if (!Database::tableExists($table)) return Database::isConnected() ? 0 : count(self::sampleRows($table));
        $row = Database::safeFetchOne("SELECT COUNT(*) total FROM `{$table}`");
        return (int)($row['total'] ?? 0);
    }

    private static function cleanData(string $table, array $input): array {
        $allowed = self::allowedColumns($table);
        $data = [];
        foreach ($allowed as $column) {
            if (!array_key_exists($column, $input)) continue;
            $value = $input[$column];
            if (is_array($value)) $value = json_encode($value, JSON_UNESCAPED_UNICODE);
            if ($column === 'slug' && trim((string)$value) === '') {
                $source = $input['name'] ?? $input['title'] ?? $input['shop_name'] ?? 'item';
                $value = \unique_slug($table, (string)$source, !empty($input['id']) ? (int)$input['id'] : null);
            }
            if ($value === '') $value = null;
            $data[$column] = $value;
        }
        return $data;
    }

    public static function save(string $table, array $input, ?int $id = null): bool {
        if (!preg_match('/^[a-z0-9_]+$/', $table) || !Database::tableExists($table)) return false;
        $data = self::cleanData($table, $input + ['id'=>$id]);
        if (!$data) return false;
        if ($id) {
            $sets = implode(',', array_map(static fn(string $column): string => "`{$column}`=?", array_keys($data)));
            if (in_array('updated_at', self::tableColumns($table), true)) $sets .= ',`updated_at`=NOW()';
            return Database::execute("UPDATE `{$table}` SET {$sets} WHERE id=?", [...array_values($data), $id]);
        }
        $columns = array_keys($data);
        $sql = "INSERT INTO `{$table}` (`" . implode('`,`', $columns) . '`) VALUES (' . implode(',', array_fill(0, count($columns), '?')) . ')';
        if (in_array('created_at', self::tableColumns($table), true)) {
            $sql = "INSERT INTO `{$table}` (`" . implode('`,`', [...$columns, 'created_at']) . '`) VALUES (' . implode(',', array_fill(0, count($columns), '?')) . ',NOW())';
        }
        return Database::execute($sql, array_values($data));
    }

    public static function quickSave(string $table, array $input): bool { return self::save($table, $input); }

    public static function delete(string $table, int $id): bool {
        if ($id < 1 || !Database::tableExists($table)) return false;
        $columns = self::tableColumns($table);
        if (in_array('status', $columns, true)) return Database::execute("UPDATE `{$table}` SET status='deleted' WHERE id=?", [$id]);
        return Database::execute("DELETE FROM `{$table}` WHERE id=? LIMIT 1", [$id]);
    }

    public static function sampleRows(string $table): array {
        if (APP_ENV === 'production') return [];
        $base = [
            ['id'=>1,'name'=>'Sample '.self::fieldLabel($table).' Record','code'=>'DEMO-001','title'=>'Demo configuration','status'=>'active','sort_order'=>1],
            ['id'=>2,'name'=>'Pending Review','code'=>'DEMO-002','title'=>'Sample pending item','status'=>'pending','sort_order'=>2],
        ];
        if ($table === 'products') return [
            ['id'=>1,'name'=>'Smart LED Television 43 inch','sku'=>'ST-LED43','barcode'=>'880100000001','regular_price'=>49900,'sale_price'=>45900,'stock'=>18,'status'=>'published'],
            ['id'=>2,'name'=>'Android POS Barcode Kit','sku'=>'ST-POSKIT','barcode'=>'880100000002','regular_price'=>28500,'sale_price'=>26900,'stock'=>9,'status'=>'published'],
        ];
        if ($table === 'orders') return [
            ['id'=>1,'order_no'=>'PH-DEMO-1001','customer_name'=>'Demo Customer','total_amount'=>5490,'payment_status'=>'paid','order_status'=>'processing','status'=>'active'],
            ['id'=>2,'order_no'=>'PH-DEMO-1002','customer_name'=>'Walk-in POS','total_amount'=>1290,'payment_status'=>'paid','order_status'=>'delivered','status'=>'active'],
        ];
        if (str_starts_with($table, 'pos_')) return [
            ['id'=>1,'name'=>'Main POS Register','code'=>'POS-01','status'=>'active','sort_order'=>1],
            ['id'=>2,'name'=>'Mobile Cashier Device','code'=>'POS-MOB','status'=>'active','sort_order'=>2],
        ];
        return $base;
    }

    public static function fieldLabel(string $field): string { return ucwords(str_replace('_', ' ', $field)); }
}
