<?php
declare(strict_types=1);
namespace Repositories;

final class AdminCatalogRepository extends BaseRepository
{
    public function dashboard(): array
    {
        $today = date('Y-m-d');
        $cards = [
            'revenue_total' => (float)($this->fetchOne('SELECT COALESCE(SUM(total_amount),0) v FROM orders WHERE payment_status IN ("paid","pending") AND order_status NOT IN ("cancelled","returned","refunded")')['v'] ?? 0),
            'revenue_today' => (float)($this->fetchOne('SELECT COALESCE(SUM(total_amount),0) v FROM orders WHERE DATE(created_at)=:d AND order_status NOT IN ("cancelled","returned","refunded")', ['d'=>$today])['v'] ?? 0),
            'orders_total' => (int)($this->fetchOne('SELECT COUNT(*) v FROM orders')['v'] ?? 0),
            'orders_today' => (int)($this->fetchOne('SELECT COUNT(*) v FROM orders WHERE DATE(created_at)=:d', ['d'=>$today])['v'] ?? 0),
            'pending_orders' => (int)($this->fetchOne('SELECT COUNT(*) v FROM orders WHERE order_status IN ("confirmed","processing","packed")')['v'] ?? 0),
            'products_total' => (int)($this->fetchOne('SELECT COUNT(*) v FROM products WHERE status != "inactive"')['v'] ?? 0),
            'low_stock' => (int)($this->fetchOne('SELECT COUNT(*) v FROM products WHERE stock_quantity <= 10 AND status="active"')['v'] ?? 0),
            'blogs_published' => (int)($this->fetchOne('SELECT COUNT(*) v FROM blogs WHERE status="published"')['v'] ?? 0),
            'bulk_leads_new' => (int)($this->fetchOne('SELECT COUNT(*) v FROM bulk_orders WHERE status="new"')['v'] ?? 0),
            'distributor_leads_new' => (int)($this->fetchOne('SELECT COUNT(*) v FROM distributor_inquiries WHERE status="new"')['v'] ?? 0),
            'contact_new' => (int)($this->fetchOne('SELECT COUNT(*) v FROM contact_messages WHERE status="new"')['v'] ?? 0),
        ];
        return [
            'cards' => $cards,
            'recent_orders' => $this->fetchAll('SELECT id,order_number,customer_name,customer_phone,order_status,payment_method,payment_status,total_amount,created_at FROM orders ORDER BY created_at DESC LIMIT 8'),
            'low_stock_products' => $this->fetchAll('SELECT id,name,sku,stock_quantity,status FROM products WHERE stock_quantity <= 10 ORDER BY stock_quantity ASC LIMIT 10'),
            'top_products' => $this->fetchAll('SELECT product_id,product_name,SUM(quantity) quantity_sold,SUM(total_price) revenue FROM order_items GROUP BY product_id,product_name ORDER BY quantity_sold DESC LIMIT 8'),
        ];
    }

    public function products(array $filters = []): array
    {
        $params = []; $where = ['1=1'];
        if (!empty($filters['search'])) { $where[]='(p.name LIKE :search OR p.sku LIKE :search OR p.slug LIKE :search)'; $params['search']='%'.$filters['search'].'%'; }
        if (!empty($filters['status'])) { $where[]='p.status=:status'; $params['status']=$filters['status']; }
        if (!empty($filters['category_id'])) { $where[]='p.category_id=:category_id'; $params['category_id']=(int)$filters['category_id']; }
        $limit=max(1,min(100,(int)($filters['limit']??100))); $offset=max(0,((int)($filters['page']??1)-1)*$limit);
        return $this->fetchAll('SELECT p.*, c.name category_name, c.slug category_slug, (SELECT image_url FROM product_images pi WHERE pi.product_id=p.id ORDER BY sort_order ASC LIMIT 1) primary_image FROM products p JOIN categories c ON c.id=p.category_id WHERE '.implode(' AND ',$where).' ORDER BY p.created_at DESC LIMIT '.$limit.' OFFSET '.$offset, $params);
    }
    public function product(int $id): ?array { $p=$this->fetchOne('SELECT p.*, c.name category_name FROM products p JOIN categories c ON c.id=p.category_id WHERE p.id=:id LIMIT 1',['id'=>$id]); if(!$p)return null; $p['images']=$this->fetchAll('SELECT * FROM product_images WHERE product_id=:id ORDER BY sort_order ASC',['id'=>$id]); return $p; }
    public function createProduct(array $d): int { $this->db->beginTransaction(); try{$stmt=$this->db->prepare('INSERT INTO products(category_id,name,slug,sku,short_description,description,ingredients,shelf_life,weight,price,mrp,stock_quantity,is_featured,status,meta_title,meta_description) VALUES(:category_id,:name,:slug,:sku,:short_description,:description,:ingredients,:shelf_life,:weight,:price,:mrp,:stock_quantity,:is_featured,:status,:meta_title,:meta_description)'); $stmt->execute($this->productParams($d)); $id=(int)$this->db->lastInsertId(); $this->replaceImages($id,$d['images']??[]); $this->db->commit(); return $id;}catch(\Throwable $e){$this->db->rollBack(); throw $e;}}
    public function updateProduct(int $id,array $d): void { $this->db->beginTransaction(); try{$params=$this->productParams($d); $params['id']=$id; $stmt=$this->db->prepare('UPDATE products SET category_id=:category_id,name=:name,slug=:slug,sku=:sku,short_description=:short_description,description=:description,ingredients=:ingredients,shelf_life=:shelf_life,weight=:weight,price=:price,mrp=:mrp,stock_quantity=:stock_quantity,is_featured=:is_featured,status=:status,meta_title=:meta_title,meta_description=:meta_description WHERE id=:id'); $stmt->execute($params); if(array_key_exists('images',$d))$this->replaceImages($id,$d['images']??[]); $this->db->commit();}catch(\Throwable $e){$this->db->rollBack(); throw $e;}}
    public function deleteProduct(int $id): void { $this->execute('UPDATE products SET status="inactive" WHERE id=:id',['id'=>$id]); }

    public function categories(): array { return $this->fetchAll('SELECT *, (SELECT COUNT(*) FROM products p WHERE p.category_id=categories.id) product_count FROM categories ORDER BY sort_order ASC, name ASC'); }
    public function createCategory(array $d): int { $stmt=$this->db->prepare('INSERT INTO categories(name,slug,description,image_url,meta_title,meta_description,sort_order,status) VALUES(:name,:slug,:description,:image_url,:meta_title,:meta_description,:sort_order,:status)'); $stmt->execute($this->categoryParams($d)); return (int)$this->db->lastInsertId(); }
    public function updateCategory(int $id,array $d): void { $p=$this->categoryParams($d); $p['id']=$id; $stmt=$this->db->prepare('UPDATE categories SET name=:name,slug=:slug,description=:description,image_url=:image_url,meta_title=:meta_title,meta_description=:meta_description,sort_order=:sort_order,status=:status WHERE id=:id'); $stmt->execute($p); }
    public function deleteCategory(int $id): void { $this->execute('UPDATE categories SET status="inactive" WHERE id=:id',['id'=>$id]); }

    public function blogs(array $filters=[]): array { $params=[];$where=['1=1']; if(!empty($filters['search'])){$where[]='(title LIKE :search OR slug LIKE :search)';$params['search']='%'.$filters['search'].'%';} if(!empty($filters['status'])){$where[]='status=:status';$params['status']=$filters['status'];} return $this->fetchAll('SELECT * FROM blogs WHERE '.implode(' AND ',$where).' ORDER BY created_at DESC LIMIT 100',$params); }
    public function blog(int $id): ?array { return $this->fetchOne('SELECT * FROM blogs WHERE id=:id LIMIT 1',['id'=>$id]); }
    public function createBlog(array $d): int { $stmt=$this->db->prepare('INSERT INTO blogs(title,slug,excerpt,content,featured_image,meta_title,meta_description,status,published_at) VALUES(:title,:slug,:excerpt,:content,:featured_image,:meta_title,:meta_description,:status,:published_at)'); $stmt->execute($this->blogParams($d)); return (int)$this->db->lastInsertId(); }
    public function updateBlog(int $id,array $d): void { $p=$this->blogParams($d); $p['id']=$id; $stmt=$this->db->prepare('UPDATE blogs SET title=:title,slug=:slug,excerpt=:excerpt,content=:content,featured_image=:featured_image,meta_title=:meta_title,meta_description=:meta_description,status=:status,published_at=:published_at WHERE id=:id'); $stmt->execute($p); }
    public function deleteBlog(int $id): void { $this->execute('UPDATE blogs SET status="draft" WHERE id=:id',['id'=>$id]); }

    public function leads(): array { return ['bulk_orders'=>$this->fetchAll('SELECT * FROM bulk_orders ORDER BY created_at DESC LIMIT 100'), 'distributor_inquiries'=>$this->fetchAll('SELECT * FROM distributor_inquiries ORDER BY created_at DESC LIMIT 100'), 'contact_messages'=>$this->fetchAll('SELECT * FROM contact_messages ORDER BY created_at DESC LIMIT 100')]; }
    public function deliveryZones(): array { return $this->fetchAll('SELECT * FROM delivery_zones ORDER BY state,city,pincode'); }
    public function upsertDeliveryZone(array $d): void { $stmt=$this->db->prepare('INSERT INTO delivery_zones(pincode,city,state,country,delivery_charge,delivery_estimate_days,is_serviceable) VALUES(:pincode,:city,:state,:country,:delivery_charge,:delivery_estimate_days,:is_serviceable) ON DUPLICATE KEY UPDATE city=VALUES(city),state=VALUES(state),country=VALUES(country),delivery_charge=VALUES(delivery_charge),delivery_estimate_days=VALUES(delivery_estimate_days),is_serviceable=VALUES(is_serviceable)'); $stmt->execute(['pincode'=>$d['pincode'],'city'=>$d['city'],'state'=>$d['state'],'country'=>$d['country']??'India','delivery_charge'=>(float)$d['delivery_charge'],'delivery_estimate_days'=>(int)$d['delivery_estimate_days'],'is_serviceable'=>!empty($d['is_serviceable'])?1:0]); }

    private function productParams(array $d): array { return ['category_id'=>(int)$d['category_id'],'name'=>$d['name'],'slug'=>$d['slug'],'sku'=>$d['sku']?:null,'short_description'=>$d['short_description']??null,'description'=>$d['description']??null,'ingredients'=>$d['ingredients']??null,'shelf_life'=>$d['shelf_life']??null,'weight'=>$d['weight']??null,'price'=>(float)$d['price'],'mrp'=>($d['mrp']??'')!==''?(float)$d['mrp']:null,'stock_quantity'=>(int)($d['stock_quantity']??0),'is_featured'=>!empty($d['is_featured'])?1:0,'status'=>$d['status']??'active','meta_title'=>$d['meta_title']??null,'meta_description'=>$d['meta_description']??null]; }
    private function categoryParams(array $d): array { return ['name'=>$d['name'],'slug'=>$d['slug'],'description'=>$d['description']??null,'image_url'=>$d['image_url']??null,'meta_title'=>$d['meta_title']??null,'meta_description'=>$d['meta_description']??null,'sort_order'=>(int)($d['sort_order']??0),'status'=>in_array($d['status']??'active',['active','inactive'],true)?$d['status']:'active']; }
    private function blogParams(array $d): array { $status=$d['status']??'draft'; return ['title'=>$d['title'],'slug'=>$d['slug'],'excerpt'=>$d['excerpt']??null,'content'=>$d['content'],'featured_image'=>$d['featured_image']??null,'meta_title'=>$d['meta_title']??null,'meta_description'=>$d['meta_description']??null,'status'=>$status,'published_at'=>$status==='published'?($d['published_at']??date('Y-m-d H:i:s')):null]; }
    private function replaceImages(int $productId,array $images): void { $this->execute('DELETE FROM product_images WHERE product_id=:id',['id'=>$productId]); foreach(array_values($images) as $i=>$img){$url=is_array($img)?($img['image_url']??''):(string)$img; if(!$url)continue; $stmt=$this->db->prepare('INSERT INTO product_images(product_id,image_url,alt_text,sort_order) VALUES(:product_id,:image_url,:alt_text,:sort_order)'); $stmt->execute(['product_id'=>$productId,'image_url'=>$url,'alt_text'=>is_array($img)?($img['alt_text']??null):null,'sort_order'=>$i]);}}
}
