<?php
namespace App\Models;
use App\Core\Database as DB;

class Event
{
    public static function all(): array
    {
        return DB::all("SELECT e.*, COUNT(p.id) as photo_count
            FROM events e LEFT JOIN photos p ON p.event_id=e.id
            GROUP BY e.id ORDER BY e.date_event DESC, e.created_at DESC");
    }
    public static function active(): array
    {
        return DB::all("SELECT e.*, COUNT(p.id) as photo_count
            FROM events e LEFT JOIN photos p ON p.event_id=e.id
            WHERE e.status='active' GROUP BY e.id ORDER BY e.date_event DESC");
    }
    public static function find(int $id): ?array   { return DB::one("SELECT * FROM events WHERE id=?", [$id]); }
    public static function bySlug(string $s): ?array { return DB::one("SELECT * FROM events WHERE slug=? AND status='active'", [$s]); }

    public static function create(array $d): int
    {
        $pw = !empty($d['password']) ? \password_hash($d['password'], PASSWORD_BCRYPT) : null;
        return DB::id("INSERT INTO events (name,slug,description,date_event,order_prefix,password) VALUES (?,?,?,?,?,?)",
            [$d['name'], $d['slug'], $d['description']??null, $d['date_event']??null, $d['order_prefix']??null, $pw]);
    }
    public static function update(int $id, array $d): void
    {
        if (!empty($d['password'])) {
            $pw = \password_hash($d['password'], PASSWORD_BCRYPT);
            DB::exec("UPDATE events SET name=?,description=?,date_event=?,status=?,order_prefix=?,password=? WHERE id=?",
                [$d['name'], $d['description']??null, $d['date_event']??null, $d['status'], $d['order_prefix']??null, $pw, $id]);
        } else {
            DB::exec("UPDATE events SET name=?,description=?,date_event=?,status=?,order_prefix=? WHERE id=?",
                [$d['name'], $d['description']??null, $d['date_event']??null, $d['status'], $d['order_prefix']??null, $id]);
        }
    }
    public static function toggle(int $id): void
    {
        DB::exec("UPDATE events SET status=IF(status='active','hidden','active') WHERE id=?", [$id]);
    }
    public static function setCover(int $id, string $path): void
    {
        DB::exec("UPDATE events SET cover_photo=? WHERE id=?", [$path, $id]);
    }
    public static function delete(int $id): void { DB::exec("DELETE FROM events WHERE id=?", [$id]); }
}

class Photo
{
    public static function forEvent(int $eid, int $page=1, int $pp=60): array
    {
        $off = ($page-1)*$pp;
        return DB::all("SELECT * FROM photos WHERE event_id=? ORDER BY sort_order,id LIMIT ? OFFSET ?", [$eid,$pp,$off]);
    }
    public static function countEvent(int $eid): int { return (int)DB::val("SELECT COUNT(*) FROM photos WHERE event_id=?",[$eid]); }
    public static function find(int $id): ?array     { return DB::one("SELECT * FROM photos WHERE id=?",[$id]); }
    public static function findMany(array $ids): array
    {
        if (!$ids) return [];
        $in = \implode(',', \array_fill(0, \count($ids), '?'));
        return DB::all("SELECT * FROM photos WHERE id IN ($in)", $ids);
    }
    public static function create(array $d): int
    {
        return DB::id("INSERT INTO photos (event_id,filename,original_path,preview_path,thumb_path) VALUES (?,?,?,?,?)",
            [$d['event_id'],$d['filename'],$d['original_path'],$d['preview_path'],$d['thumb_path']]);
    }
    public static function delete(int $id): ?array
    {
        $p = self::find($id);
        if ($p) DB::exec("DELETE FROM photos WHERE id=?", [$id]);
        return $p;
    }
    public static function forOrder(int $orderId): array
    {
        return DB::all("SELECT p.* FROM photos p
            JOIN order_items oi ON oi.photo_id=p.id WHERE oi.order_id=?", [$orderId]);
    }
}

class Order
{
    public static function all(array $filters=[]): array
    {
        $where=[]; $params=[];
        if (!empty($filters['event_id'])) { $where[]='event_id=?'; $params[]=$filters['event_id']; }
        if (!empty($filters['status']))   { $where[]='status=?';   $params[]=$filters['status']; }
        if (!empty($filters['q'])) {
            $where[]='(customer_name LIKE ? OR email LIKE ? OR order_number LIKE ?)';
            $q="%{$filters['q']}%"; $params=\array_merge($params,[$q,$q,$q]);
        }
        $w = $where ? 'WHERE '.\implode(' AND ',$where) : '';
        return DB::all("SELECT * FROM orders $w ORDER BY created_at DESC", $params);
    }
    public static function find(int $id): ?array  { return DB::one("SELECT * FROM orders WHERE id=?",[$id]); }
    public static function byNumber(string $n): ?array { return DB::one("SELECT * FROM orders WHERE order_number=?",[$n]); }
    public static function items(int $id): array
    {
        return DB::all("SELECT oi.*, p.thumb_path FROM order_items oi
            LEFT JOIN photos p ON p.id=oi.photo_id WHERE oi.order_id=?", [$id]);
    }
    public static function stats(): array
    {
        return DB::one("SELECT COUNT(*) total, SUM(status='pending') pending,
            SUM(status='paid') paid, SUM(status='sent') sent,
            COALESCE(SUM(IF(status IN('paid','sent'),total,0)),0) revenue FROM orders") ?? [];
    }
    public static function recent(int $n=10): array
    {
        return DB::all("SELECT * FROM orders ORDER BY created_at DESC LIMIT ?", [$n]);
    }
    public static function nextNumber(string $prefix, int $eventId): string
    {
        $count = (int)DB::val("SELECT COUNT(*) FROM orders WHERE event_id=?", [$eventId]);
        return \strtoupper($prefix ?: 'ENC') . '-F' . \str_pad($count+1, 3, '0', STR_PAD_LEFT);
    }
    public static function create(array $d): int
    {
        return DB::id("INSERT INTO orders
            (event_id,event_name,order_number,customer_name,phone,email,nif,
             shipping_method,address,subtotal,shipping_cost,total,photos_free,payment_method,status)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,'pending')",
            [$d['event_id']??null,$d['event_name']??'',$d['order_number'],
             $d['customer_name'],$d['phone'],$d['email']??'',$d['nif']??'',
             $d['shipping_method'],$d['address']??null,
             $d['subtotal'],$d['shipping_cost'],$d['total'],$d['photos_free'],
             $d['payment_method']??'dinheiro']);
    }
    public static function update(int $id, array $d): void
    {
        DB::exec("UPDATE orders SET customer_name=?,phone=?,email=?,nif=?,
            shipping_method=?,address=?,payment_method=?,status=?,notes=?,total=? WHERE id=?",
            [$d['customer_name'],$d['phone'],$d['email']??'',$d['nif']??'',
             $d['shipping_method'],$d['address']??null,
             $d['payment_method']??'dinheiro',$d['status'],$d['notes']??null,$d['total'],$id]);
    }
}

class OrderItem
{
    public static function create(array $d): int
    {
        return DB::id("INSERT INTO order_items (order_id,photo_id,filename,quantity,unit_price,subtotal)
            VALUES (?,?,?,?,?,?)",
            [$d['order_id'],$d['photo_id'],$d['filename'],$d['quantity'],$d['unit_price'],$d['subtotal']]);
    }
    public static function deleteByOrder(int $orderId): void
    {
        DB::exec("DELETE FROM order_items WHERE order_id=?", [$orderId]);
    }
}
