<?php
namespace App\Repositories;

use App\Database\Connection;
use App\Models\Activity;
use App\Utils\Logger;
use PDO;
use PDOException;

class ActivityRepository
{
    private $db;

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    public function findByDidarId($didarId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM activities WHERE didar_activity_id = ? LIMIT 1");
        $stmt->execute([$didarId]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        return $data ? new Activity($data) : null;
    }

    /**
     * Point lookup by local primary key. Used as a fallback when an activity has no
     * didar_activity_id yet (local-only activities created while Didar sync is disabled).
     */
    public function findById($id)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM activities WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        $data = $stmt->fetch(PDO::FETCH_ASSOC);
        return $data ? new Activity($data) : null;
    }

    /**
     * In-place update of the scheduling fields for a local activity addressed by its
     * primary key. Used when an activity has no didar_activity_id (so the upsert-by-Didar-id
     * path in save() would otherwise create a duplicate row).
     */
    public function updateScheduledById($id, array $fields)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare(
            "UPDATE activities SET
                activity_type_id = ?,
                activity_type_title = ?,
                due_date = ?,
                is_scheduled = ?,
                reminder_enabled = ?,
                last_update_time = ?,
                last_sync = ?
             WHERE id = ?"
        );
        $stmt->execute([
            $fields['activity_type_id'] ?? null,
            $fields['activity_type_title'] ?? null,
            $fields['due_date'] ?? null,
            $fields['is_scheduled'] ?? 0,
            $fields['reminder_enabled'] ?? 0,
            $fields['last_update_time'] ?? null,
            $fields['last_sync'] ?? null,
            $id,
        ]);
        return $stmt->rowCount();
    }

    public function findByContactId($contactId, $limit = 50)
    {
        $pdo = $this->db->getPdo();
        // Ensure all columns including due_date, is_scheduled, reminder_enabled, last_update_time are selected
        $stmt = $pdo->prepare("SELECT * FROM activities WHERE contact_didar_id = ? ORDER BY register_date DESC LIMIT ?");
        $stmt->execute([$contactId, $limit]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return array_map(function($data) {
            return new Activity($data);
        }, $results);
    }

    /**
     * Lean projection for VirtualStageService::calculate(): selects only the columns the
     * stage engine reads, so the per-save stage recompute doesn't transfer raw_json LONGTEXT
     * for up to $limit rows. Ordering preserved so [0] is the most recent activity.
     */
    public function findByContactIdForStage($contactId, $limit = 50)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT activity_type_title, result_note, stage, register_date
                               FROM activities WHERE contact_didar_id = ? ORDER BY register_date DESC LIMIT ?");
        $stmt->execute([$contactId, (int)$limit]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return array_map(function ($data) {
            return new Activity($data);
        }, $results);
    }

    public function findByDealId($dealId, $limit = 100)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM activities WHERE deal_didar_id = ? ORDER BY register_date DESC LIMIT ?");
        $stmt->execute([$dealId, $limit]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return array_map(function($data) {
            return new Activity($data);
        }, $results);
    }

    public function save(Activity $activity)
    {
        $pdo = $this->db->getPdo();
        // MySQL: INSERT ... ON DUPLICATE KEY UPDATE
	        $sql = "INSERT INTO activities 
	                (didar_activity_id, activity_type_id, activity_type_title, owner_didar_id, deal_didar_id,
	                 contact_didar_id, title, note, result_note, failure_reason_code, failure_reason_description, is_done, due_date, done_date, duration, call_type,
	                 activity_category, direction, stage, is_scheduled, reminder_enabled, register_date, last_update_time, last_sync, raw_json)
	                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	                ON DUPLICATE KEY UPDATE
	                activity_type_id = VALUES(activity_type_id),
	                activity_type_title = VALUES(activity_type_title),
	                owner_didar_id = VALUES(owner_didar_id),
	                deal_didar_id = VALUES(deal_didar_id),
	                contact_didar_id = VALUES(contact_didar_id),
	                title = VALUES(title),
	                note = VALUES(note),
	                result_note = VALUES(result_note),
	                failure_reason_code = VALUES(failure_reason_code),
	                failure_reason_description = VALUES(failure_reason_description),
	                is_done = VALUES(is_done),
	                due_date = VALUES(due_date),
	                done_date = VALUES(done_date),
	                duration = VALUES(duration),
	                call_type = VALUES(call_type),
	                activity_category = VALUES(activity_category),
                direction = VALUES(direction),
                stage = VALUES(stage),
                is_scheduled = VALUES(is_scheduled),
                reminder_enabled = VALUES(reminder_enabled),
                register_date = VALUES(register_date),
                last_update_time = VALUES(last_update_time),
                last_sync = VALUES(last_sync),
                raw_json = VALUES(raw_json)";
        
	        $stmt = $pdo->prepare($sql);
	        $stmt->execute([
	            $activity->didar_activity_id,
	            $activity->activity_type_id,
	            $activity->activity_type_title,
	            $activity->owner_didar_id,
	            $activity->deal_didar_id,
	            $activity->contact_didar_id,
	            $activity->title,
	            $activity->note,
	            $activity->result_note,
	            $activity->failure_reason_code ?? null,
	            $activity->failure_reason_description ?? null,
	            $activity->is_done ?? 0,
	            $activity->due_date,
	            $activity->done_date,
	            $activity->duration,
	            $activity->call_type ?? null,
	            $activity->activity_category,
	            $activity->direction,
	            $activity->stage,
	            $activity->is_scheduled ?? 0,
	            $activity->reminder_enabled ?? 0,
	            $activity->register_date,
	            $activity->last_update_time,
	            $activity->last_sync,
	            $activity->raw_json
	        ]);
        
        return $pdo->lastInsertId();
    }

    public function delete($activityId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM activities WHERE didar_activity_id = ?");
        $stmt->execute([$activityId]);
        // Local-only activities have no didar_activity_id; the frontend then sends the
        // numeric primary key. Didar ids are GUIDs, so a numeric id can't collide.
        if ($stmt->rowCount() === 0 && is_numeric($activityId)) {
            $byId = $pdo->prepare("DELETE FROM activities WHERE id = ?");
            return $byId->execute([(int) $activityId]);
        }
        return true;
    }

    public function deleteByContactId(string $contactId): int
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM activities WHERE contact_didar_id = ?");
        $stmt->execute([$contactId]);
        return (int)$stmt->rowCount();
    }

    /**
     * Check if any activity exists for a specific deal (local DB only).
     */
    public function hasActivityForDeal($dealId): bool
    {
        if (empty($dealId)) {
            return false;
        }
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT 1 FROM activities WHERE deal_didar_id = ? LIMIT 1");
        $stmt->execute([$dealId]);
        return (bool) $stmt->fetchColumn();
    }

    public function getLastActivityDate($contactId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT MAX(register_date) FROM activities WHERE contact_didar_id = ?");
        $stmt->execute([$contactId]);
        return $stmt->fetchColumn();
    }
}

