<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Tasks_model extends CI_Model
{
	protected $app_db;

	public function __construct()
	{
		parent::__construct();
		$this->app_db = $this->load->database('app_write', TRUE);
		$this->load->model('App_write_model');
		$this->load->model('Gala_read_model');
	}

	public function run_migration()
	{
		$dbforge = $this->load->dbforge($this->app_db, TRUE);
		$this->ensure_task_templates_table($dbforge);
		$this->ensure_task_template_items_table($dbforge);
		$this->ensure_task_rules_table($dbforge);
		$this->ensure_task_rule_excluded_items_table($dbforge);
		$this->ensure_task_assignments_table($dbforge);
		$this->ensure_task_assignment_items_table($dbforge);
		$this->ensure_task_assignment_notes_table($dbforge);
		$this->ensure_special_dates_table($dbforge);
		$this->seed_special_dates();
	}

	public function get_templates($active_only = FALSE)
	{
		$this->app_db->from('task_templates');
		if ($active_only)
		{
			$this->app_db->where('is_active', 1);
		}
		$this->app_db->order_by('name', 'ASC');
		$templates = $this->app_db->get()->result_array();
		return $this->attach_template_items($templates);
	}

	public function get_template($template_id)
	{
		$template = $this->app_db->get_where('task_templates', array('id' => (int) $template_id))->row_array();
		if (!$template)
		{
			return null;
		}

		$templates = $this->attach_template_items(array($template));
		return !empty($templates) ? $templates[0] : $template;
	}

	public function save_template($data, $template_id = null)
	{
		$template_id = $template_id !== null ? (int) $template_id : null;
		$current = $template_id ? $this->get_template($template_id) : null;
		$payload = array(
			'name' => isset($data['name']) ? trim((string) $data['name']) : ($current ? $current['name'] : ''),
			'description' => isset($data['description']) ? trim((string) $data['description']) : ($current ? $current['description'] : ''),
			'is_active' => isset($data['is_active']) ? (int) !empty($data['is_active']) : ($current ? (int) $current['is_active'] : 1),
			'updated_at' => date('Y-m-d H:i:s'),
		);

		if ($current)
		{
			$this->app_db->where('id', $template_id)->update('task_templates', $payload);
			return $template_id;
		}

		$payload['created_at'] = date('Y-m-d H:i:s');
		$this->app_db->insert('task_templates', $payload);
		return (int) $this->app_db->insert_id();
	}

	public function replace_template_items($template_id, $items)
	{
		$template_id = (int) $template_id;
		if ($template_id <= 0)
		{
			return FALSE;
		}

		$this->ensure_task_template_items_table($this->load->dbforge($this->app_db, TRUE));
		$this->app_db->where('template_id', $template_id)->delete('task_template_items');
		$sort_order = 0;
		$last_parent_id = 0;
		foreach ((array) $items as $item)
		{
			$name = trim((string) (is_array($item) && isset($item['name']) ? $item['name'] : $item));
			if ($name === '')
			{
				continue;
			}

			$level = is_array($item) && isset($item['level']) ? (int) $item['level'] : 0;
			$parent_item_id = ($level > 0 && $last_parent_id > 0) ? $last_parent_id : null;
			$this->app_db->insert('task_template_items', array(
				'template_id' => $template_id,
				'name' => $name,
				'parent_item_id' => $parent_item_id,
				'sort_order' => $sort_order++,
				'is_active' => 1,
			));

			if ($level <= 0)
			{
				$last_parent_id = (int) $this->app_db->insert_id();
			}
		}

		return TRUE;
	}

	public function get_rules($active_only = FALSE)
	{
		$this->app_db->from('task_rules');
		if ($active_only)
		{
			$this->app_db->where('is_active', 1);
		}
		$this->app_db->order_by('id', 'DESC');
		$rules = $this->app_db->get()->result_array();
		return $this->attach_rule_metadata($rules);
	}

	public function get_rule($rule_id)
	{
		$rule = $this->app_db->get_where('task_rules', array('id' => (int) $rule_id))->row_array();
		if (!$rule)
		{
			return null;
		}

		$rules = $this->attach_rule_metadata(array($rule));
		return !empty($rules) ? $rules[0] : $rule;
	}

	public function save_rule($data, $rule_id = null)
	{
		$rule_id = $rule_id !== null ? (int) $rule_id : null;
		$current = $rule_id ? $this->get_rule($rule_id) : null;
		$payload = array(
			'title' => isset($data['title']) ? trim((string) $data['title']) : ($current && isset($current['title']) ? $current['title'] : ''),
			'template_id' => isset($data['template_id']) ? (int) $data['template_id'] : ($current ? (int) $current['template_id'] : 0),
			'assignment_mode' => isset($data['assignment_mode']) ? trim((string) $data['assignment_mode']) : ($current ? $current['assignment_mode'] : 'employee'),
			'employee_id' => array_key_exists('employee_id', $data) && $data['employee_id'] !== '' ? (int) $data['employee_id'] : null,
			'group_id' => array_key_exists('group_id', $data) && $data['group_id'] !== '' ? (int) $data['group_id'] : null,
			'group_distribution_mode' => isset($data['group_distribution_mode']) ? trim((string) $data['group_distribution_mode']) : ($current ? $current['group_distribution_mode'] : 'all_members'),
			'distribution_algorithm' => isset($data['distribution_algorithm']) ? trim((string) $data['distribution_algorithm']) : ($current ? $current['distribution_algorithm'] : 'random'),
			'recurrence_type' => isset($data['recurrence_type']) ? trim((string) $data['recurrence_type']) : ($current ? $current['recurrence_type'] : 'one_time'),
			'special_date_code' => !empty($data['special_date_code']) ? trim((string) $data['special_date_code']) : null,
			'special_date_lead_days' => isset($data['special_date_lead_days']) ? max(0, (int) $data['special_date_lead_days']) : ($current && isset($current['special_date_lead_days']) ? (int) $current['special_date_lead_days'] : 0),
			'one_time_date' => !empty($data['one_time_date']) ? trim((string) $data['one_time_date']) : null,
			'starts_on' => !empty($data['starts_on']) ? trim((string) $data['starts_on']) : null,
			'ends_on' => !empty($data['ends_on']) ? trim((string) $data['ends_on']) : null,
			'requires_note_on_complete' => isset($data['requires_note_on_complete']) ? (int) !empty($data['requires_note_on_complete']) : ($current ? (int) $current['requires_note_on_complete'] : 0),
			'is_active' => isset($data['is_active']) ? (int) !empty($data['is_active']) : ($current ? (int) $current['is_active'] : 1),
			'updated_at' => date('Y-m-d H:i:s'),
		);

		if ($payload['assignment_mode'] === 'global')
		{
			$payload['employee_id'] = null;
			$payload['group_id'] = null;
			$payload['group_distribution_mode'] = 'all_members';
			$payload['distribution_algorithm'] = 'random';
		}

		if ($payload['group_distribution_mode'] !== 'distributed')
		{
			$payload['distribution_algorithm'] = 'random';
		}

		if ($current)
		{
			$this->app_db->where('id', $rule_id)->update('task_rules', $payload);
			return $rule_id;
		}

		$payload['last_rotation_index'] = 0;
		$payload['created_at'] = date('Y-m-d H:i:s');
		$this->app_db->insert('task_rules', $payload);
		return (int) $this->app_db->insert_id();
	}

	public function replace_rule_excluded_items($rule_id, $item_ids)
	{
		$rule_id = (int) $rule_id;
		if ($rule_id <= 0)
		{
			return FALSE;
		}

		$this->app_db->where('rule_id', $rule_id)->delete('task_rule_excluded_items');
		$clean_ids = array();
		foreach ((array) $item_ids as $item_id)
		{
			$item_id = (int) $item_id;
			if ($item_id > 0)
			{
				$clean_ids[$item_id] = $item_id;
			}
		}

		foreach ($clean_ids as $item_id)
		{
			$this->app_db->insert('task_rule_excluded_items', array(
				'rule_id' => $rule_id,
				'template_item_id' => $item_id,
			));
		}

		return TRUE;
	}

	public function set_template_active($template_id, $is_active)
	{
		$template_id = (int) $template_id;
		$template = $this->get_template($template_id);
		if (!$template)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la plantilla.');
		}

		$now = date('Y-m-d H:i:s');
		$this->app_db->where('id', $template_id)->update('task_templates', array(
			'is_active' => $is_active ? 1 : 0,
			'updated_at' => $now,
		));

		if (!$is_active)
		{
			$this->app_db->where('template_id', $template_id)->update('task_rules', array(
				'is_active' => 0,
				'updated_at' => $now,
			));

			return array('success' => TRUE, 'message' => 'Plantilla desactivada. Las reglas asociadas quedaron en pausa.');
		}

		return array('success' => TRUE, 'message' => 'Plantilla activada correctamente.');
	}

	public function set_rule_active($rule_id, $is_active)
	{
		$rule_id = (int) $rule_id;
		$rule = $this->get_rule($rule_id);
		if (!$rule)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la regla.');
		}

		$this->app_db->where('id', $rule_id)->update('task_rules', array(
			'is_active' => $is_active ? 1 : 0,
			'updated_at' => date('Y-m-d H:i:s'),
		));

		return array(
			'success' => TRUE,
			'message' => $is_active ? 'Regla activada correctamente.' : 'Regla desactivada correctamente.',
		);
	}

	public function delete_rule($rule_id)
	{
		$rule_id = (int) $rule_id;
		if ($rule_id <= 0)
		{
			return array('success' => FALSE, 'message' => 'Regla invalida.');
		}

		$rule = $this->get_rule($rule_id);
		if (!$rule)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la tarea.');
		}

		$assignment_ids = array();
		$this->app_db->select('id');
		$this->app_db->from('task_assignments');
		$this->app_db->where('rule_id', $rule_id);
		foreach ($this->app_db->get()->result_array() as $row)
		{
			if (!empty($row['id']))
			{
				$assignment_ids[] = (int) $row['id'];
			}
		}

		if (!empty($assignment_ids))
		{
			$this->app_db->where_in('assignment_id', $assignment_ids)->delete('task_assignment_notes');
			$this->app_db->where_in('assignment_id', $assignment_ids)->delete('task_assignment_items');
		}

		$this->app_db->where('rule_id', $rule_id)->delete('task_assignments');
		$this->app_db->where('rule_id', $rule_id)->delete('task_rule_excluded_items');
		$this->app_db->where('id', $rule_id)->delete('task_rules');

		return array('success' => TRUE, 'message' => 'Tarea eliminada correctamente.');
	}

	public function delete_template($template_id)
	{
		$template_id = (int) $template_id;
		if ($template_id <= 0)
		{
			return array('success' => FALSE, 'message' => 'Plantilla invalida.');
		}

		$template = $this->get_template($template_id);
		if (!$template)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la plantilla.');
		}

		$rule_ids = array();
		$this->app_db->select('id');
		$this->app_db->from('task_rules');
		$this->app_db->where('template_id', $template_id);
		foreach ($this->app_db->get()->result_array() as $row)
		{
			if (!empty($row['id']))
			{
				$rule_ids[] = (int) $row['id'];
			}
		}

		foreach ($rule_ids as $rule_id)
		{
			$result = $this->delete_rule($rule_id);
			if (empty($result['success']))
			{
				return $result;
			}
		}

		$this->app_db->where('template_id', $template_id)->delete('task_template_items');
		$this->app_db->where('id', $template_id)->delete('task_templates');

		return array('success' => TRUE, 'message' => 'Plantilla eliminada correctamente.');
	}

	public function get_special_dates($active_only = TRUE)
	{
		if ($active_only)
		{
			$this->app_db->where('is_active', 1);
		}
		$this->app_db->order_by('sort_order', 'ASC');
		$this->app_db->order_by('name', 'ASC');
		return $this->app_db->get('special_dates')->result_array();
	}

	public function get_next_due_date_for_rule($rule, $from_date = null, $lookahead_days = 730)
	{
		if (empty($rule) || empty($rule['is_active']))
		{
			return null;
		}

		$from_date = trim((string) $from_date);
		if ($from_date === '')
		{
			$from_date = date('Y-m-d');
		}

		try
		{
			$cursor = new DateTime($from_date);
		}
		catch (Exception $exception)
		{
			return null;
		}

		for ($offset = 0; $offset <= (int) $lookahead_days; $offset++)
		{
			$candidate = $cursor->format('Y-m-d');
			if ($this->rule_is_due_on_date($rule, $candidate))
			{
				return $candidate;
			}
			$cursor->modify('+1 day');
		}

		return null;
	}

	public function get_assignments_for_employee($employee_id, $status_filter = 'open')
	{
		$employee_id = (int) $employee_id;
		if ($employee_id <= 0)
		{
			return array();
		}

		$this->app_db->from('task_assignments');
		$this->app_db->where('employee_id', $employee_id);
		if ($status_filter === 'open')
		{
			$this->app_db->where_in('status', array('pending', 'in_progress', 'overdue'));
			$this->app_db->order_by('FIELD(status, "overdue", "pending", "in_progress", "completed")', '', FALSE);
			$this->app_db->order_by('assigned_date', 'DESC');
		}
		elseif ($status_filter === 'completed')
		{
			$this->app_db->where('status', 'completed');
			$this->app_db->where('DATE(completed_at)', date('Y-m-d'));
			$this->app_db->order_by('completed_at', 'DESC');
		}
		else
		{
			$this->app_db->order_by('FIELD(status, "overdue", "pending", "in_progress", "completed")', '', FALSE);
			$this->app_db->order_by('assigned_date', 'DESC');
		}
		$assignments = $this->app_db->get()->result_array();
		return $this->attach_assignment_items($assignments);
	}

	public function get_assignments_for_admin($filters = array())
	{
		$filters = is_array($filters) ? $filters : array();
		$this->app_db->select('
			task_assignments.*,
			task_rules.group_id AS rule_group_id,
			task_rules.assignment_mode,
			task_rules.group_distribution_mode,
			task_templates.name AS template_name
		', FALSE);
		$this->app_db->from('task_assignments');
		$this->app_db->join('task_rules', 'task_rules.id = task_assignments.rule_id', 'left');
		$this->app_db->join('task_templates', 'task_templates.id = task_assignments.template_id', 'left');

		if (!empty($filters['employee_id']))
		{
			$this->app_db->where('task_assignments.employee_id', (int) $filters['employee_id']);
		}

		if (!empty($filters['group_id']))
		{
			$this->app_db->where('task_rules.group_id', (int) $filters['group_id']);
		}

		if (!empty($filters['status']) && $filters['status'] !== 'all')
		{
			$this->app_db->where('task_assignments.status', (string) $filters['status']);
		}

		$this->app_db->order_by('task_assignments.assigned_date', 'DESC');
		$this->app_db->order_by('task_assignments.id', 'DESC');
		$assignments = $this->app_db->get()->result_array();
		return $this->attach_assignment_items($assignments);
	}

	public function get_assignment_for_admin($assignment_id)
	{
		$assignment_id = (int)$assignment_id;
		if ($assignment_id <= 0)
		{
			return null;
		}

		$this->app_db->from('task_assignments');
		$this->app_db->where('id', $assignment_id);
		$assignments = $this->app_db->get()->result_array();
		$assignments = $this->attach_assignment_items($assignments);
		return !empty($assignments) ? $assignments[0] : null;
	}

	public function add_assignment_note_admin($assignment_id, $note, $created_by_employee_id = null)
	{
		$assignment_id = (int)$assignment_id;
		$note = trim((string)$note);
		if ($assignment_id <= 0 || $note === '')
		{
			return array('success' => FALSE, 'message' => 'Nota invalida.');
		}

		$assignment = $this->app_db->get_where('task_assignments', array('id' => $assignment_id))->row_array();
		if (!$assignment)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la tarea indicada.');
		}

		$this->ensure_task_assignment_notes_table($this->load->dbforge($this->app_db, TRUE));
		$this->app_db->insert('task_assignment_notes', array(
			'assignment_id' => $assignment_id,
			'note_type' => 'admin',
			'note_text' => $note,
			'created_by_employee_id' => $created_by_employee_id ? (int)$created_by_employee_id : null,
			'created_at' => date('Y-m-d H:i:s'),
		));

		return array('success' => TRUE, 'message' => 'Nota agregada.');
	}

	public function get_assignments_for_employee_range($employee_id, $date_start, $date_end, $statuses = array())
	{
		$employee_id = (int) $employee_id;
		$date_start = trim((string) $date_start);
		$date_end = trim((string) $date_end);
		if ($employee_id <= 0 || $date_start === '' || $date_end === '')
		{
			return array();
		}

		$this->app_db->from('task_assignments');
		$this->app_db->where('employee_id', $employee_id);
		$this->app_db->where('assigned_date >=', $date_start);
		$this->app_db->where('assigned_date <=', $date_end);
		$clean_statuses = array_values(array_filter(array_map('strval', (array) $statuses)));
		if (!empty($clean_statuses))
		{
			$this->app_db->where_in('status', $clean_statuses);
		}
		$this->app_db->order_by('assigned_date', 'ASC');
		$this->app_db->order_by('id', 'ASC');
		$assignments = $this->app_db->get()->result_array();
		return $this->attach_assignment_items($assignments);
	}

	public function complete_assignment($assignment_id, $employee_id, $note = '', $force_complete = FALSE)
	{
		$assignment_id = (int) $assignment_id;
		$employee_id = (int) $employee_id;
		$force_complete = $force_complete ? TRUE : FALSE;
		if ($assignment_id <= 0 || $employee_id <= 0)
		{
			return array('success' => FALSE, 'message' => 'Tarea invalida.');
		}

		$assignment = $this->app_db->get_where('task_assignments', array(
			'id' => $assignment_id,
			'employee_id' => $employee_id,
		))->row_array();

		if (!$assignment)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la tarea indicada.');
		}

		if (isset($assignment['requires_note_on_complete']) && (int) $assignment['requires_note_on_complete'] === 1 && trim((string) $note) === '')
		{
			return array('success' => FALSE, 'message' => 'Esta tarea requiere una nota para poder terminarse.');
		}

		if (isset($assignment['status']) && $assignment['status'] === 'completed')
		{
			return array('success' => TRUE, 'message' => 'La tarea ya estaba completada.');
		}

		$items = $this->app_db->get_where('task_assignment_items', array(
			'assignment_id' => $assignment_id,
		))->result_array();
		if (!$force_complete && !empty($items))
		{
			foreach ($items as $item)
			{
				if (!isset($item['status']) || $item['status'] !== 'completed')
				{
					return array('success' => FALSE, 'message' => 'Marca todas las subtareas antes de terminar la tarea.');
				}
			}
		}

		$now = date('Y-m-d H:i:s');
		$this->app_db->where('id', $assignment_id)->update('task_assignments', array(
			'status' => 'completed',
			'note' => trim((string) $note),
			'completed_at' => $now,
			'verified_by_employee_id' => null,
			'verified_by_name' => null,
			'verified_at' => null,
			'updated_at' => $now,
		));

		$this->app_db->where('assignment_id', $assignment_id)->update('task_assignment_items', array(
			'status' => 'completed',
			'completed_at' => $now,
		));

		if (trim((string) $note) !== '')
		{
			$this->app_db->insert('task_assignment_notes', array(
				'assignment_id' => $assignment_id,
				'note_type' => 'completion',
				'note_text' => trim((string) $note),
				'created_by_employee_id' => $employee_id,
				'created_at' => $now,
			));
		}

		$this->App_write_model->increment_task_completed_counter($employee_id, 1);

		return array('success' => TRUE, 'message' => 'Tarea completada correctamente.');
	}

	public function add_assignment_note($assignment_id, $employee_id, $note)
	{
		$assignment_id = (int) $assignment_id;
		$employee_id = (int) $employee_id;
		$note = trim((string) $note);
		if ($assignment_id <= 0 || $employee_id <= 0 || $note === '')
		{
			return array('success' => FALSE, 'message' => 'Escribe una nota valida.');
		}

		$assignment = $this->app_db->get_where('task_assignments', array(
			'id' => $assignment_id,
			'employee_id' => $employee_id,
		))->row_array();
		if (!$assignment)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la tarea.');
		}

		$this->app_db->insert('task_assignment_notes', array(
			'assignment_id' => $assignment_id,
			'note_type' => 'manual',
			'note_text' => $note,
			'created_by_employee_id' => $employee_id,
			'created_at' => date('Y-m-d H:i:s'),
		));

		return array('success' => TRUE, 'message' => 'Nota guardada correctamente.');
	}

	public function reactivate_assignment($assignment_id)
	{
		$assignment_id = (int) $assignment_id;
		if ($assignment_id <= 0)
		{
			return array('success' => FALSE, 'message' => 'Tarea invalida.');
		}

		$assignment = $this->app_db->get_where('task_assignments', array(
			'id' => $assignment_id,
		))->row_array();
		if (!$assignment)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la tarea.');
		}

		$items = $this->app_db->get_where('task_assignment_items', array(
			'assignment_id' => $assignment_id,
		))->result_array();
		$now = date('Y-m-d H:i:s');
		$status = isset($assignment['status']) ? (string) $assignment['status'] : 'pending';

		if ($status === 'completed')
		{
			$this->app_db->where('assignment_id', $assignment_id)->update('task_assignment_items', array(
				'status' => 'pending',
				'completed_at' => null,
			));
			$new_status = 'pending';
		}
		else
		{
			$new_status = $this->resolve_progress_status_from_items($items);
		}

		$this->app_db->where('id', $assignment_id)->update('task_assignments', array(
			'status' => $new_status,
			'note' => '',
			'completed_at' => null,
			'verified_by_employee_id' => null,
			'verified_by_name' => null,
			'verified_at' => null,
			'overdue_at' => null,
			'updated_at' => $now,
		));
		$this->app_db->where('assignment_id', $assignment_id)->delete('task_assignment_notes');

		return array('success' => TRUE, 'message' => 'Tarea reactivada correctamente.');
	}

	public function verify_assignment($assignment_id, $verifier_employee_id, $verifier_name = '')
	{
		$assignment_id = (int) $assignment_id;
		$verifier_employee_id = (int) $verifier_employee_id;
		$verifier_name = trim((string) $verifier_name);
		if ($assignment_id <= 0 || $verifier_employee_id <= 0)
		{
			return array('success' => FALSE, 'message' => 'No pudimos verificar la tarea.');
		}

		$assignment = $this->app_db->get_where('task_assignments', array(
			'id' => $assignment_id,
		))->row_array();
		if (!$assignment)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la tarea.');
		}

		if ((string) $assignment['status'] !== 'completed')
		{
			return array('success' => FALSE, 'message' => 'Solo puedes verificar tareas completadas.');
		}

		$now = date('Y-m-d H:i:s');
		$this->app_db->where('id', $assignment_id)->update('task_assignments', array(
			'verified_by_employee_id' => $verifier_employee_id,
			'verified_by_name' => $verifier_name,
			'verified_at' => $now,
			'updated_at' => $now,
		));

		$this->app_db->insert('task_assignment_notes', array(
			'assignment_id' => $assignment_id,
			'note_type' => 'verification',
			'note_text' => $verifier_name !== '' ? 'Verificada por '.$verifier_name : 'Tarea verificada.',
			'created_by_employee_id' => $verifier_employee_id,
			'created_at' => $now,
		));

		return array('success' => TRUE, 'message' => 'Tarea verificada correctamente.');
	}
	public function update_assignment_admin($assignment_id, $data = array())
	{
		$assignment_id = (int) $assignment_id;
		$data = is_array($data) ? $data : array();
		if ($assignment_id <= 0)
		{
			return array('success' => FALSE, 'message' => 'Tarea invalida.');
		}

		$assignment = $this->app_db->get_where('task_assignments', array(
			'id' => $assignment_id,
		))->row_array();
		if (!$assignment)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la tarea.');
		}

		$title = isset($data['title']) ? trim((string) $data['title']) : (string) $assignment['title'];
		$description = isset($data['description']) ? trim((string) $data['description']) : (string) $assignment['description'];
		$due_date = array_key_exists('due_date', $data) ? trim((string) $data['due_date']) : (string) $assignment['due_date'];

		if ($title === '')
		{
			return array('success' => FALSE, 'message' => 'El titulo es obligatorio.');
		}

		if ($due_date !== '')
		{
			$date = DateTime::createFromFormat('Y-m-d', $due_date);
			if (!$date || $date->format('Y-m-d') !== $due_date)
			{
				return array('success' => FALSE, 'message' => 'La fecha de vencimiento no es valida.');
			}
		}
		else
		{
			$due_date = null;
		}

		$this->app_db->where('id', $assignment_id)->update('task_assignments', array(
			'title' => $title,
			'description' => $description,
			'due_date' => $due_date,
			'updated_at' => date('Y-m-d H:i:s'),
		));

		return array('success' => TRUE, 'message' => 'Tarea actualizada correctamente.');
	}

	public function update_assignment_item_status($assignment_id, $assignment_item_id, $employee_id, $is_completed)
	{
		$assignment_id = (int) $assignment_id;
		$assignment_item_id = (int) $assignment_item_id;
		$employee_id = (int) $employee_id;
		if ($assignment_id <= 0 || $assignment_item_id <= 0 || $employee_id <= 0)
		{
			return array('success' => FALSE, 'message' => 'Datos invalidos.');
		}

		$assignment = $this->app_db->get_where('task_assignments', array(
			'id' => $assignment_id,
			'employee_id' => $employee_id,
		))->row_array();
		if (!$assignment)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la tarea.');
		}

		if (isset($assignment['status']) && $assignment['status'] === 'completed')
		{
			return array('success' => FALSE, 'message' => 'La tarea ya fue completada.');
		}

		$item = $this->app_db->get_where('task_assignment_items', array(
			'id' => $assignment_item_id,
			'assignment_id' => $assignment_id,
		))->row_array();
		if (!$item)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la subtarea.');
		}

		$this->app_db->where('id', $assignment_item_id)->update('task_assignment_items', array(
			'status' => $is_completed ? 'completed' : 'pending',
			'completed_at' => $is_completed ? date('Y-m-d H:i:s') : null,
		));

		return $this->refresh_assignment_progress_state($assignment_id);
	}

	public function get_assignment_summary_for_employee($employee_id)
	{
		$employee_id = (int) $employee_id;
		$summary = array(
			'pending' => 0,
			'overdue' => 0,
			'completed' => 0,
			'total_open' => 0,
		);
		if ($employee_id <= 0)
		{
			return $summary;
		}

		$this->app_db->select('status, COUNT(*) AS total_rows', FALSE);
		$this->app_db->from('task_assignments');
		$this->app_db->where('employee_id', $employee_id);
		$this->app_db->group_by('status');
		$rows = $this->app_db->get()->result_array();
		foreach ($rows as $row)
		{
			$status = isset($row['status']) ? (string) $row['status'] : '';
			$total = isset($row['total_rows']) ? (int) $row['total_rows'] : 0;
			if ($status === 'completed')
			{
				$summary['completed'] += $total;
				continue;
			}

			if ($status === 'overdue')
			{
				$summary['overdue'] += $total;
			}
			else
			{
				$summary['pending'] += $total;
			}

			$summary['total_open'] += $total;
		}

		return $summary;
	}

	public function create_quick_assignment($employee_id, $title, $description = '')
	{
		$employee_id = (int) $employee_id;
		$title = trim((string) $title);
		$description = trim((string) $description);
		if ($employee_id <= 0 || $title === '')
		{
			return array('success' => FALSE, 'message' => 'Debes indicar empleado y nombre de la tarea.');
		}

		$today = date('Y-m-d');
		$now = date('Y-m-d H:i:s');
		$payload = array(
			'rule_id' => 0,
			'template_id' => 0,
			'employee_id' => $employee_id,
			'title' => $title,
			'description' => $description,
			'assigned_date' => $today,
			'due_date' => $today,
			'status' => 'pending',
			'note' => '',
			'requires_note_on_complete' => 0,
			'completed_at' => null,
			'overdue_at' => null,
			'created_at' => $now,
			'updated_at' => $now,
		);

		$this->app_db->insert('task_assignments', $payload);
		$assignment_id = (int) $this->app_db->insert_id();
		if ($assignment_id <= 0)
		{
			return array('success' => FALSE, 'message' => 'No pudimos crear la tarea rapida.');
		}

		return array(
			'success' => TRUE,
			'message' => 'Tarea creada correctamente.',
			'assignment_id' => $assignment_id,
		);
	}

	public function sync_due_assignments($reference_date = null)
	{
		$today = $reference_date ? trim((string) $reference_date) : date('Y-m-d');
		$rules = $this->get_rules(TRUE);
		if (empty($rules))
		{
			return;
		}

		$active_employees = $this->get_active_employee_index();
		foreach ($rules as $rule)
		{
			if (!$this->rule_is_due_on_date($rule, $today))
			{
				continue;
			}

			$template = !empty($rule['template']) ? $rule['template'] : $this->get_template(isset($rule['template_id']) ? $rule['template_id'] : 0);
			if (!$template || empty($template['is_active']))
			{
				continue;
			}

			$items = $this->get_rule_effective_items($rule, $template);
			$employee_item_map = $this->resolve_employee_item_map_for_rule($rule, $items, $active_employees);
			if (empty($employee_item_map))
			{
				continue;
			}

			foreach ($employee_item_map as $employee_id => $assigned_items)
			{
				$employee_id = (int) $employee_id;
				if ($employee_id <= 0)
				{
					continue;
				}

				if ($this->assignment_exists_for_rule_employee_date((int) $rule['id'], $employee_id, $today))
				{
					continue;
				}

				$this->close_previous_open_assignments($rule, $employee_id, $today);
				$assignment_id = $this->create_assignment($rule, $template, $employee_id, $today);
				$this->replace_assignment_items($assignment_id, $assigned_items);
			}
		}
	}

	protected function get_rule_effective_items($rule, $template)
	{
		$excluded_ids = !empty($rule['excluded_item_ids']) ? array_map('intval', $rule['excluded_item_ids']) : array();
		$assignable_items = isset($template['assignable_items']) ? (array) $template['assignable_items'] : array();
		if (empty($assignable_items))
		{
			return $this->build_default_assignment_items($template);
		}

		$items = array();
		foreach ($assignable_items as $item)
		{
			$item_id = isset($item['id']) ? (int) $item['id'] : 0;
			if ($item_id > 0 && in_array($item_id, $excluded_ids, TRUE))
			{
				continue;
			}
			$items[] = $item;
		}

		return $items;
	}

	protected function resolve_employee_item_map_for_rule($rule, $items, $active_employees)
	{
		if ((string) $rule['assignment_mode'] === 'global')
		{
			$map = array();
			foreach ((array) $active_employees as $employee_id => $employee)
			{
				$employee_id = (int) $employee_id;
				if ($employee_id <= 0)
				{
					continue;
				}

				$map[$employee_id] = $items;
			}

			return $map;
		}

		if ((string) $rule['assignment_mode'] === 'employee')
		{
			$employee_id = isset($rule['employee_id']) ? (int) $rule['employee_id'] : 0;
			if ($employee_id <= 0 || !isset($active_employees[$employee_id]))
			{
				return array();
			}

			return array($employee_id => $items);
		}

		$group_id = isset($rule['group_id']) ? (int) $rule['group_id'] : 0;
		if ($group_id <= 0)
		{
			return array();
		}

		$members = array_values(array_filter(
			$this->App_write_model->get_employee_group_members($group_id),
			function ($employee_id) use ($active_employees) {
				return isset($active_employees[(int) $employee_id]);
			}
		));

		if (empty($members))
		{
			return array();
		}

		$mode = isset($rule['group_distribution_mode']) ? (string) $rule['group_distribution_mode'] : 'all_members';
		if ($mode !== 'distributed')
		{
			$map = array();
			foreach ($members as $employee_id)
			{
				$map[(int) $employee_id] = $items;
			}
			return $map;
		}

		$algorithm = isset($rule['distribution_algorithm']) ? (string) $rule['distribution_algorithm'] : 'random';
		if (empty($items))
		{
			$employee_id = $algorithm === 'fair_rotation'
				? $members[(int) $rule['last_rotation_index'] % count($members)]
				: $members[array_rand($members)];

			if ($algorithm === 'fair_rotation')
			{
				$this->update_rule_rotation_index((int) $rule['id'], ((int) $rule['last_rotation_index']) + 1);
			}

			return array((int) $employee_id => array());
		}

		if ($algorithm === 'fair_rotation')
		{
			$start = (int) $rule['last_rotation_index'];
			$map = array();
			foreach (array_values($items) as $offset => $item)
			{
				$employee_id = $members[($start + $offset) % count($members)];
				if (!isset($map[$employee_id]))
				{
					$map[$employee_id] = array();
				}
				$map[$employee_id][] = $item;
			}
			$this->update_rule_rotation_index((int) $rule['id'], $start + count($items));
			return $map;
		}

		$shuffled_members = $members;
		shuffle($shuffled_members);
		$shuffled_items = array_values($items);
		shuffle($shuffled_items);
		$map = array();
		foreach ($shuffled_items as $offset => $item)
		{
			$employee_id = $shuffled_members[$offset % count($shuffled_members)];
			if (!isset($map[$employee_id]))
			{
				$map[$employee_id] = array();
			}
			$map[$employee_id][] = $item;
		}
		return $map;
	}

	protected function update_rule_rotation_index($rule_id, $rotation_index)
	{
		$this->app_db->where('id', (int) $rule_id)->update('task_rules', array(
			'last_rotation_index' => max(0, (int) $rotation_index),
			'updated_at' => date('Y-m-d H:i:s'),
		));
	}

	protected function assignment_exists_for_rule_employee_date($rule_id, $employee_id, $assigned_date)
	{
		return $this->app_db->get_where('task_assignments', array(
			'rule_id' => (int) $rule_id,
			'employee_id' => (int) $employee_id,
			'assigned_date' => (string) $assigned_date,
		))->row_array() ? TRUE : FALSE;
	}

	protected function close_previous_open_assignments($rule, $employee_id, $today)
	{
		$this->app_db->where('rule_id', (int) $rule['id']);
		$this->app_db->where('employee_id', (int) $employee_id);
		$this->app_db->where('assigned_date <', $today);
		$this->app_db->where_in('status', array('pending', 'in_progress', 'overdue'));
		$this->app_db->update('task_assignments', array(
			'status' => 'overdue',
			'overdue_at' => date('Y-m-d H:i:s'),
			'updated_at' => date('Y-m-d H:i:s'),
		));
	}

	protected function create_assignment($rule, $template, $employee_id, $assigned_date)
	{
		$payload = array(
			'rule_id' => (int) $rule['id'],
			'template_id' => (int) $template['id'],
			'employee_id' => (int) $employee_id,
			'title' => !empty($rule['title']) ? $rule['title'] : (isset($template['name']) ? $template['name'] : 'Tarea'),
			'description' => isset($template['description']) ? $template['description'] : '',
			'assigned_date' => (string) $assigned_date,
			'due_date' => (string) $assigned_date,
			'status' => 'pending',
			'note' => '',
			'requires_note_on_complete' => !empty($rule['requires_note_on_complete']) ? 1 : 0,
			'created_at' => date('Y-m-d H:i:s'),
			'updated_at' => date('Y-m-d H:i:s'),
		);

		$this->app_db->insert('task_assignments', $payload);
		return (int) $this->app_db->insert_id();
	}

	protected function replace_assignment_items($assignment_id, $items)
	{
		$this->app_db->where('assignment_id', (int) $assignment_id)->delete('task_assignment_items');
		foreach ((array) $items as $item)
		{
			$this->app_db->insert('task_assignment_items', array(
				'assignment_id' => (int) $assignment_id,
				'template_item_id' => isset($item['id']) ? (int) $item['id'] : null,
				'name' => isset($item['name']) ? (string) $item['name'] : 'Subtarea',
				'status' => 'pending',
			));
		}
	}

	protected function get_active_employee_index()
	{
		$employees = $this->Gala_read_model->get_employees_for_visor();
		$app_users = $this->App_write_model->get_all_app_users_index();
		$index = array();
		foreach ($employees as $employee)
		{
			$employee_id = isset($employee['person_id']) ? (int) $employee['person_id'] : 0;
			if ($employee_id <= 0 || empty($employee['is_active_in_gala']))
			{
				continue;
			}

			$app_user = isset($app_users[$employee_id]) ? $app_users[$employee_id] : null;
			$is_enabled = !$app_user || !isset($app_user['is_enabled']) || (int) $app_user['is_enabled'] === 1;
			if ($is_enabled)
			{
				$index[$employee_id] = $employee;
			}
		}

		return $index;
	}

	protected function rule_is_due_on_date($rule, $date)
	{
		$date = trim((string) $date);
		if ($date === '' || empty($rule['is_active']))
		{
			return FALSE;
		}

		if (!empty($rule['starts_on']) && $date < $rule['starts_on'])
		{
			return FALSE;
		}

		if (!empty($rule['ends_on']) && $date > $rule['ends_on'])
		{
			return FALSE;
		}

		$type = isset($rule['recurrence_type']) ? (string) $rule['recurrence_type'] : 'one_time';
		switch ($type)
		{
			case 'daily':
				return TRUE;

			case 'weekly':
				return !empty($rule['starts_on']) && date('N', strtotime($date)) === date('N', strtotime($rule['starts_on']));

			case 'monthly':
				return !empty($rule['starts_on']) && date('d', strtotime($date)) === date('d', strtotime($rule['starts_on']));

			case 'quarterly':
				return !empty($rule['starts_on'])
					&& date('d', strtotime($date)) === date('d', strtotime($rule['starts_on']))
					&& ((int) date('n', strtotime($date)) - (int) date('n', strtotime($rule['starts_on']))) % 3 === 0;

			case 'semiannual':
				return !empty($rule['starts_on'])
					&& date('d', strtotime($date)) === date('d', strtotime($rule['starts_on']))
					&& ((int) date('n', strtotime($date)) - (int) date('n', strtotime($rule['starts_on']))) % 6 === 0;

			case 'annual':
				return !empty($rule['starts_on'])
					&& date('m-d', strtotime($date)) === date('m-d', strtotime($rule['starts_on']));

			case 'special_date':
				if (empty($rule['special_date']))
				{
					return FALSE;
				}
				$lead_days = isset($rule['special_date_lead_days']) ? max(0, (int) $rule['special_date_lead_days']) : 0;
				$occurrences = $this->resolve_special_date_occurrences($rule['special_date'], (int) date('Y', strtotime($date)));
				foreach ($occurrences as $occurrence)
				{
					$trigger_date = $occurrence;
					if ($lead_days > 0)
					{
						$trigger_date = date('Y-m-d', strtotime($occurrence.' -'.$lead_days.' days'));
					}
					if ($trigger_date === $date)
					{
						return TRUE;
					}
				}
				return FALSE;

			case 'one_time':
			default:
				return !empty($rule['one_time_date']) && $rule['one_time_date'] === $date;
		}
	}

	protected function attach_template_items($templates)
	{
		if (empty($templates))
		{
			return array();
		}

		$template_ids = array();
		foreach ($templates as $template)
		{
			$template_ids[] = (int) $template['id'];
		}

		$this->app_db->from('task_template_items');
		$this->app_db->where_in('template_id', $template_ids);
		$this->app_db->where('is_active', 1);
		$this->app_db->order_by('sort_order', 'ASC');
		$this->app_db->order_by('id', 'ASC');
		$item_rows = $this->app_db->get()->result_array();

		$items_index = array();
		$child_index = array();
		foreach ($item_rows as $row)
		{
			$template_id = isset($row['template_id']) ? (int) $row['template_id'] : 0;
			if (!isset($items_index[$template_id]))
			{
				$items_index[$template_id] = array();
			}
			$items_index[$template_id][] = $row;

			$parent_item_id = !empty($row['parent_item_id']) ? (int) $row['parent_item_id'] : 0;
			if ($parent_item_id > 0)
			{
				$child_index[$parent_item_id] = TRUE;
			}
		}

		foreach ($templates as &$template)
		{
			$template_id = isset($template['id']) ? (int) $template['id'] : 0;
			$template['items'] = isset($items_index[$template_id]) ? $items_index[$template_id] : array();
			$template['assignable_items'] = array();
			foreach ($template['items'] as $item)
			{
				$item_id = isset($item['id']) ? (int) $item['id'] : 0;
				$parent_item_id = !empty($item['parent_item_id']) ? (int) $item['parent_item_id'] : 0;
				$item['display_prefix'] = $parent_item_id > 0 ? '- ' : '';
				if ($parent_item_id > 0 || !isset($child_index[$item_id]))
				{
					$template['assignable_items'][] = $item;
				}
			}
		}
		unset($template);

		return $templates;
	}

	protected function attach_rule_metadata($rules)
	{
		if (empty($rules))
		{
			return array();
		}

		$special_codes = array();
		$rule_ids = array();
		foreach ($rules as $rule)
		{
			$rule_ids[] = (int) $rule['id'];
			if (!empty($rule['special_date_code']))
			{
				$special_codes[] = (string) $rule['special_date_code'];
			}
		}

		$templates = array();
		foreach ($this->get_templates() as $template)
		{
			$templates[(int) $template['id']] = $template;
		}

		$special_dates = array();
		if (!empty($special_codes))
		{
			$this->app_db->where_in('code', array_unique($special_codes));
			foreach ($this->app_db->get('special_dates')->result_array() as $row)
			{
				$special_dates[(string) $row['code']] = $row;
			}
		}

		$excluded_index = array();
		if (!empty($rule_ids))
		{
			$this->app_db->where_in('rule_id', $rule_ids);
			$rows = $this->app_db->get('task_rule_excluded_items')->result_array();
			foreach ($rows as $row)
			{
				$rule_id = isset($row['rule_id']) ? (int) $row['rule_id'] : 0;
				if (!isset($excluded_index[$rule_id]))
				{
					$excluded_index[$rule_id] = array();
				}
				$excluded_index[$rule_id][] = (int) $row['template_item_id'];
			}
		}

		foreach ($rules as &$rule)
		{
			$rule['template'] = isset($templates[(int) $rule['template_id']]) ? $templates[(int) $rule['template_id']] : null;
			$rule['special_date'] = !empty($rule['special_date_code']) && isset($special_dates[$rule['special_date_code']])
				? $special_dates[$rule['special_date_code']]
				: null;
			$rule['excluded_item_ids'] = isset($excluded_index[(int) $rule['id']]) ? $excluded_index[(int) $rule['id']] : array();
		}
		unset($rule);

		return $rules;
	}

	protected function attach_assignment_items($assignments)
	{
		if (empty($assignments))
		{
			return array();
		}

		$assignment_ids = array();
		foreach ($assignments as $assignment)
		{
			$assignment_ids[] = (int) $assignment['id'];
		}

		$this->app_db->where_in('assignment_id', $assignment_ids);
		$this->app_db->order_by('id', 'ASC');
		$rows = $this->app_db->get('task_assignment_items')->result_array();
		$items_index = array();
		foreach ($rows as $row)
		{
			$assignment_id = isset($row['assignment_id']) ? (int) $row['assignment_id'] : 0;
			if (!isset($items_index[$assignment_id]))
			{
				$items_index[$assignment_id] = array();
			}
			$items_index[$assignment_id][] = $row;
		}

		$missing_assignment_ids = array();
		foreach ($assignments as $assignment)
		{
			$assignment_id = isset($assignment['id']) ? (int) $assignment['id'] : 0;
			if ($assignment_id <= 0)
			{
				continue;
			}
			if (empty($items_index[$assignment_id]) && $this->backfill_assignment_items_if_missing($assignment))
			{
				$missing_assignment_ids[] = $assignment_id;
			}
		}

		if (!empty($missing_assignment_ids))
		{
			$this->app_db->where_in('assignment_id', array_values(array_unique($missing_assignment_ids)));
			$this->app_db->order_by('id', 'ASC');
			$rows = $this->app_db->get('task_assignment_items')->result_array();
			foreach ($rows as $row)
			{
				$assignment_id = isset($row['assignment_id']) ? (int) $row['assignment_id'] : 0;
				if (!isset($items_index[$assignment_id]))
				{
					$items_index[$assignment_id] = array();
				}
				$items_index[$assignment_id][] = $row;
			}
		}

		$notes_index = array();
		$assignment_ids = array_values(array_unique($assignment_ids));
		if (!empty($assignment_ids))
		{
			$this->app_db->where_in('assignment_id', $assignment_ids);
			$this->app_db->order_by('created_at', 'DESC');
			$note_rows = $this->app_db->get('task_assignment_notes')->result_array();
			foreach ($note_rows as $row)
			{
				$assignment_id = isset($row['assignment_id']) ? (int) $row['assignment_id'] : 0;
				if (!isset($notes_index[$assignment_id]))
				{
					$notes_index[$assignment_id] = array();
				}
				$notes_index[$assignment_id][] = $row;
			}
		}

		foreach ($assignments as &$assignment)
		{
			$assignment['items'] = isset($items_index[(int) $assignment['id']]) ? $items_index[(int) $assignment['id']] : array();
			$assignment['notes'] = isset($notes_index[(int) $assignment['id']]) ? $notes_index[(int) $assignment['id']] : array();
			$assignment['items_total'] = count($assignment['items']);
			$assignment['items_completed'] = 0;
			foreach ($assignment['items'] as $item)
			{
				if (isset($item['status']) && $item['status'] === 'completed')
				{
					$assignment['items_completed']++;
				}
			}
		}
		unset($assignment);

		return $assignments;
	}

	protected function backfill_assignment_items_if_missing($assignment)
	{
		$assignment_id = isset($assignment['id']) ? (int) $assignment['id'] : 0;
		$template_id = isset($assignment['template_id']) ? (int) $assignment['template_id'] : 0;
		$rule_id = isset($assignment['rule_id']) ? (int) $assignment['rule_id'] : 0;
		if ($assignment_id <= 0 || $template_id <= 0)
		{
			return FALSE;
		}

		$template = $this->get_template($template_id);
		if (!$template)
		{
			return FALSE;
		}

		$items = !empty($template['assignable_items'])
			? $template['assignable_items']
			: $this->build_default_assignment_items($template);
		if ($rule_id > 0)
		{
			$rule = $this->get_rule($rule_id);
			if ($rule)
			{
				$items = $this->get_rule_effective_items($rule, $template);
			}
		}

		if (empty($items))
		{
			return FALSE;
		}

		$this->replace_assignment_items($assignment_id, $items);
		return TRUE;
	}

	protected function build_default_assignment_items($template)
	{
		$title = isset($template['name']) ? trim((string) $template['name']) : '';
		if ($title === '')
		{
			return array();
		}

		return array(array(
			'id' => 0,
			'name' => $title,
		));
	}

	protected function refresh_assignment_progress_state($assignment_id)
	{
		$assignment = $this->app_db->get_where('task_assignments', array(
			'id' => (int) $assignment_id,
		))->row_array();
		if (!$assignment)
		{
			return array('success' => FALSE, 'message' => 'No encontramos la tarea.');
		}

		$items = $this->app_db->get_where('task_assignment_items', array(
			'assignment_id' => (int) $assignment_id,
		))->result_array();

		$total_items = count($items);
		$completed_items = 0;
		foreach ($items as $item)
		{
			if (isset($item['status']) && $item['status'] === 'completed')
			{
				$completed_items++;
			}
		}

		$status = isset($assignment['status']) ? (string) $assignment['status'] : 'pending';
		if ($status !== 'completed')
		{
			$status = $status === 'overdue'
				? 'overdue'
				: $this->resolve_progress_status_from_items($items);

			$this->app_db->where('id', (int) $assignment_id)->update('task_assignments', array(
				'status' => $status,
				'updated_at' => date('Y-m-d H:i:s'),
			));
		}

		return array(
			'success' => TRUE,
			'message' => 'Subtarea actualizada.',
			'status' => $status,
			'items_total' => $total_items,
			'items_completed' => $completed_items,
			'all_completed' => $total_items > 0 ? $completed_items >= $total_items : TRUE,
		);
	}

	protected function resolve_progress_status_from_items($items)
	{
		$completed_items = 0;
		$total_items = count((array) $items);
		foreach ((array) $items as $item)
		{
			if (isset($item['status']) && $item['status'] === 'completed')
			{
				$completed_items++;
			}
		}

		return $completed_items > 0 ? 'in_progress' : 'pending';
	}

	protected function resolve_special_date_occurrences($special_date, $year)
	{
		$year = max(2000, (int) $year);
		$type = isset($special_date['calc_type']) ? (string) $special_date['calc_type'] : 'fixed';
		$config = json_decode(isset($special_date['calc_config_json']) ? (string) $special_date['calc_config_json'] : '{}', TRUE);
		if (!is_array($config))
		{
			$config = array();
		}

		switch ($type)
		{
			case 'nth_weekday_of_month':
				$date = $this->resolve_nth_weekday_of_month(
					$year,
					isset($config['month']) ? (int) $config['month'] : 1,
					isset($config['weekday']) ? (int) $config['weekday'] : 0,
					isset($config['nth']) ? (int) $config['nth'] : 1
				);
				return $date ? array($date) : array();

			case 'easter_offset':
				$offset = isset($config['offset_days']) ? (int) $config['offset_days'] : 0;
				return array($this->resolve_easter_offset_date($year, $offset));

			case 'fixed':
			default:
				return array(sprintf(
					'%04d-%02d-%02d',
					$year,
					isset($config['month']) ? (int) $config['month'] : 1,
					isset($config['day']) ? (int) $config['day'] : 1
				));
		}
	}

	protected function resolve_nth_weekday_of_month($year, $month, $weekday, $nth)
	{
		$date = new DateTime(sprintf('%04d-%02d-01', $year, $month));
		$matches = 0;
		while ((int) $date->format('n') === (int) $month)
		{
			if ((int) $date->format('w') === (int) $weekday)
			{
				$matches++;
				if ($matches === (int) $nth)
				{
					return $date->format('Y-m-d');
				}
			}
			$date->modify('+1 day');
		}

		return null;
	}

	protected function resolve_easter_offset_date($year, $offset_days)
	{
		$base = new DateTime($this->resolve_easter_sunday($year));
		$base->modify(((int) $offset_days >= 0 ? '+' : '').(int) $offset_days.' days');
		return $base->format('Y-m-d');
	}

	protected function resolve_easter_sunday($year)
	{
		$a = $year % 19;
		$b = (int) floor($year / 100);
		$c = $year % 100;
		$d = (int) floor($b / 4);
		$e = $b % 4;
		$f = (int) floor(($b + 8) / 25);
		$g = (int) floor(($b - $f + 1) / 3);
		$h = (19 * $a + $b - $d - $g + 15) % 30;
		$i = (int) floor($c / 4);
		$k = $c % 4;
		$l = (32 + 2 * $e + 2 * $i - $h - $k) % 7;
		$m = (int) floor(($a + 11 * $h + 22 * $l) / 451);
		$month = (int) floor(($h + $l - 7 * $m + 114) / 31);
		$day = (($h + $l - 7 * $m + 114) % 31) + 1;
		return sprintf('%04d-%02d-%02d', $year, $month, $day);
	}

	protected function seed_special_dates()
	{
		$rows = array(
			array('code' => 'divina_pastora', 'category' => 'Religiosa', 'name' => 'Dia de la Divina Pastora', 'calc_type' => 'fixed', 'config' => array('month' => 1, 'day' => 14), 'sort_order' => 10),
			array('code' => 'teachers_day', 'category' => 'Gremial', 'name' => 'Dia del Maestro', 'calc_type' => 'fixed', 'config' => array('month' => 1, 'day' => 15), 'sort_order' => 20),
			array('code' => 'new_year', 'category' => 'Civil', 'name' => 'Ano Nuevo', 'calc_type' => 'fixed', 'config' => array('month' => 1, 'day' => 1), 'sort_order' => 30),
			array('code' => 'virgen_candelaria', 'category' => 'Religiosa', 'name' => 'Virgen de la Candelaria', 'calc_type' => 'fixed', 'config' => array('month' => 2, 'day' => 2), 'sort_order' => 40),
			array('code' => 'youth_day', 'category' => 'Patria / Juvenil', 'name' => 'Dia de la Juventud', 'calc_type' => 'fixed', 'config' => array('month' => 2, 'day' => 12), 'sort_order' => 50),
			array('code' => 'love_friendship', 'category' => 'Civil', 'name' => 'Dia del Amor y la Amistad', 'calc_type' => 'fixed', 'config' => array('month' => 2, 'day' => 14), 'sort_order' => 60),
			array('code' => 'carnival_monday', 'category' => 'Religiosa', 'name' => 'Lunes de Carnaval', 'calc_type' => 'easter_offset', 'config' => array('offset_days' => -48), 'sort_order' => 70),
			array('code' => 'carnival_tuesday', 'category' => 'Religiosa', 'name' => 'Martes de Carnaval', 'calc_type' => 'easter_offset', 'config' => array('offset_days' => -47), 'sort_order' => 80),
			array('code' => 'doctors_day', 'category' => 'Gremial', 'name' => 'Dia del Medico', 'calc_type' => 'fixed', 'config' => array('month' => 3, 'day' => 10), 'sort_order' => 90),
			array('code' => 'san_jose', 'category' => 'Religiosa', 'name' => 'Dia de San Jose', 'calc_type' => 'fixed', 'config' => array('month' => 3, 'day' => 19), 'sort_order' => 100),
			array('code' => 'independence_declaration', 'category' => 'Patria', 'name' => 'Declaracion de la Independencia', 'calc_type' => 'fixed', 'config' => array('month' => 4, 'day' => 19), 'sort_order' => 110),
			array('code' => 'holy_thursday', 'category' => 'Religiosa', 'name' => 'Jueves Santo', 'calc_type' => 'easter_offset', 'config' => array('offset_days' => -3), 'sort_order' => 120),
			array('code' => 'holy_friday', 'category' => 'Religiosa', 'name' => 'Viernes Santo', 'calc_type' => 'easter_offset', 'config' => array('offset_days' => -2), 'sort_order' => 130),
			array('code' => 'labor_day', 'category' => 'Civil', 'name' => 'Dia del Trabajador', 'calc_type' => 'fixed', 'config' => array('month' => 5, 'day' => 1), 'sort_order' => 140),
			array('code' => 'cruz_de_mayo', 'category' => 'Tradicion', 'name' => 'Cruz de Mayo', 'calc_type' => 'fixed', 'config' => array('month' => 5, 'day' => 3), 'sort_order' => 150),
			array('code' => 'mothers_day', 'category' => 'Homenaje', 'name' => 'Dia de la Madre', 'calc_type' => 'nth_weekday_of_month', 'config' => array('month' => 5, 'weekday' => 0, 'nth' => 2), 'sort_order' => 160),
			array('code' => 'fathers_day', 'category' => 'Homenaje', 'name' => 'Dia del Padre', 'calc_type' => 'nth_weekday_of_month', 'config' => array('month' => 6, 'weekday' => 0, 'nth' => 3), 'sort_order' => 170),
			array('code' => 'carabobo', 'category' => 'Patria', 'name' => 'Batalla de Carabobo', 'calc_type' => 'fixed', 'config' => array('month' => 6, 'day' => 24), 'sort_order' => 180),
			array('code' => 'journalists_day', 'category' => 'Gremial', 'name' => 'Dia del Periodista', 'calc_type' => 'fixed', 'config' => array('month' => 6, 'day' => 27), 'sort_order' => 190),
			array('code' => 'childrens_day', 'category' => 'Homenaje', 'name' => 'Dia del Nino', 'calc_type' => 'nth_weekday_of_month', 'config' => array('month' => 7, 'weekday' => 0, 'nth' => 3), 'sort_order' => 200),
			array('code' => 'acta_independence', 'category' => 'Patria', 'name' => 'Firma del Acta de Independencia', 'calc_type' => 'fixed', 'config' => array('month' => 7, 'day' => 5), 'sort_order' => 210),
			array('code' => 'bolivar_birth', 'category' => 'Patria', 'name' => 'Natalicio de Simon Bolivar', 'calc_type' => 'fixed', 'config' => array('month' => 7, 'day' => 24), 'sort_order' => 220),
			array('code' => 'diablos_de_yare', 'category' => 'Cultura', 'name' => 'Diablos de Yare', 'calc_type' => 'easter_offset', 'config' => array('offset_days' => 60), 'sort_order' => 230),
			array('code' => 'virgen_valle', 'category' => 'Religiosa', 'name' => 'Virgen del Valle', 'calc_type' => 'fixed', 'config' => array('month' => 9, 'day' => 8), 'sort_order' => 240),
			array('code' => 'indigenous_resistance', 'category' => 'Patria', 'name' => 'Dia de la Resistencia Indigena', 'calc_type' => 'fixed', 'config' => array('month' => 10, 'day' => 12), 'sort_order' => 250),
			array('code' => 'chinita', 'category' => 'Religiosa', 'name' => 'Virgen de la Chinita', 'calc_type' => 'fixed', 'config' => array('month' => 11, 'day' => 18), 'sort_order' => 260),
			array('code' => 'bolivar_death', 'category' => 'Conmemorativa', 'name' => 'Muerte de Simon Bolivar', 'calc_type' => 'fixed', 'config' => array('month' => 12, 'day' => 17), 'sort_order' => 270),
			array('code' => 'christmas_eve', 'category' => 'Religiosa', 'name' => 'Nochebuena', 'calc_type' => 'fixed', 'config' => array('month' => 12, 'day' => 24), 'sort_order' => 280),
			array('code' => 'christmas', 'category' => 'Religiosa', 'name' => 'Navidad', 'calc_type' => 'fixed', 'config' => array('month' => 12, 'day' => 25), 'sort_order' => 290),
		);

		foreach ($rows as $row)
		{
			$current = $this->app_db->get_where('special_dates', array('code' => $row['code']))->row_array();
			$payload = array(
				'category' => $row['category'],
				'name' => $row['name'],
				'calc_type' => $row['calc_type'],
				'calc_config_json' => json_encode($row['config']),
				'is_active' => 1,
				'sort_order' => $row['sort_order'],
				'updated_at' => date('Y-m-d H:i:s'),
			);

			if ($current)
			{
				$this->app_db->where('code', $row['code'])->update('special_dates', $payload);
			}
			else
			{
				$payload['code'] = $row['code'];
				$payload['created_at'] = date('Y-m-d H:i:s');
				$this->app_db->insert('special_dates', $payload);
			}
		}
	}

	protected function ensure_task_templates_table($dbforge)
	{
		if ($this->app_db->table_exists('task_templates'))
		{
			return;
		}

		$dbforge->add_field(array(
			'id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE),
			'name' => array('type' => 'VARCHAR', 'constraint' => 255, 'default' => ''),
			'description' => array('type' => 'TEXT', 'null' => TRUE),
			'is_active' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 1),
			'created_at' => array('type' => 'DATETIME', 'null' => TRUE),
			'updated_at' => array('type' => 'DATETIME', 'null' => TRUE),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->create_table('task_templates', TRUE);
	}

	protected function ensure_task_template_items_table($dbforge)
	{
		if ($this->app_db->table_exists('task_template_items'))
		{
			if (!$this->app_db->field_exists('parent_item_id', 'task_template_items'))
			{
				$dbforge->add_column('task_template_items', array(
					'parent_item_id' => array('type' => 'INT', 'constraint' => 10, 'null' => TRUE),
				));
			}
			return;
		}

		$dbforge->add_field(array(
			'id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE),
			'template_id' => array('type' => 'INT', 'constraint' => 10),
			'name' => array('type' => 'VARCHAR', 'constraint' => 255, 'default' => ''),
			'parent_item_id' => array('type' => 'INT', 'constraint' => 10, 'null' => TRUE),
			'sort_order' => array('type' => 'INT', 'constraint' => 10, 'default' => 0),
			'is_active' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 1),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->add_key('template_id');
		$dbforge->create_table('task_template_items', TRUE);
	}

	protected function ensure_task_rules_table($dbforge)
	{
		if ($this->app_db->table_exists('task_rules'))
		{
			if (!$this->app_db->field_exists('special_date_lead_days', 'task_rules'))
			{
				$dbforge->add_column('task_rules', array(
					'special_date_lead_days' => array('type' => 'INT', 'constraint' => 10, 'default' => 0),
				));
			}
			if (!$this->app_db->field_exists('title', 'task_rules'))
			{
				$dbforge->add_column('task_rules', array(
					'title' => array('type' => 'VARCHAR', 'constraint' => 255, 'default' => ''),
				));
			}
			return;
		}

		$dbforge->add_field(array(
			'id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE),
			'title' => array('type' => 'VARCHAR', 'constraint' => 255, 'default' => ''),
			'template_id' => array('type' => 'INT', 'constraint' => 10),
			'assignment_mode' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => 'employee'),
			'employee_id' => array('type' => 'INT', 'constraint' => 10, 'null' => TRUE),
			'group_id' => array('type' => 'INT', 'constraint' => 10, 'null' => TRUE),
			'group_distribution_mode' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => 'all_members'),
			'distribution_algorithm' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => 'random'),
			'recurrence_type' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => 'one_time'),
			'special_date_code' => array('type' => 'VARCHAR', 'constraint' => 60, 'null' => TRUE),
			'special_date_lead_days' => array('type' => 'INT', 'constraint' => 10, 'default' => 0),
			'one_time_date' => array('type' => 'DATE', 'null' => TRUE),
			'starts_on' => array('type' => 'DATE', 'null' => TRUE),
			'ends_on' => array('type' => 'DATE', 'null' => TRUE),
			'requires_note_on_complete' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0),
			'last_rotation_index' => array('type' => 'INT', 'constraint' => 10, 'default' => 0),
			'is_active' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 1),
			'created_at' => array('type' => 'DATETIME', 'null' => TRUE),
			'updated_at' => array('type' => 'DATETIME', 'null' => TRUE),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->add_key('template_id');
		$dbforge->create_table('task_rules', TRUE);
	}

	protected function ensure_task_rule_excluded_items_table($dbforge)
	{
		if ($this->app_db->table_exists('task_rule_excluded_items'))
		{
			return;
		}

		$dbforge->add_field(array(
			'id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE),
			'rule_id' => array('type' => 'INT', 'constraint' => 10),
			'template_item_id' => array('type' => 'INT', 'constraint' => 10),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->add_key('rule_id');
		$dbforge->create_table('task_rule_excluded_items', TRUE);
	}

	protected function ensure_task_assignments_table($dbforge)
	{
		if ($this->app_db->table_exists('task_assignments'))
		{
			if (!$this->app_db->field_exists('verified_by_employee_id', 'task_assignments'))
			{
				$dbforge->add_column('task_assignments', array(
					'verified_by_employee_id' => array('type' => 'INT', 'constraint' => 10, 'null' => TRUE),
				));
			}
			if (!$this->app_db->field_exists('verified_by_name', 'task_assignments'))
			{
				$dbforge->add_column('task_assignments', array(
					'verified_by_name' => array('type' => 'VARCHAR', 'constraint' => 255, 'null' => TRUE),
				));
			}
			if (!$this->app_db->field_exists('verified_at', 'task_assignments'))
			{
				$dbforge->add_column('task_assignments', array(
					'verified_at' => array('type' => 'DATETIME', 'null' => TRUE),
				));
			}
			return;
		}

		$dbforge->add_field(array(
			'id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE),
			'rule_id' => array('type' => 'INT', 'constraint' => 10),
			'template_id' => array('type' => 'INT', 'constraint' => 10),
			'employee_id' => array('type' => 'INT', 'constraint' => 10),
			'title' => array('type' => 'VARCHAR', 'constraint' => 255, 'default' => ''),
			'description' => array('type' => 'TEXT', 'null' => TRUE),
			'assigned_date' => array('type' => 'DATE'),
			'due_date' => array('type' => 'DATE', 'null' => TRUE),
			'status' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => 'pending'),
			'note' => array('type' => 'TEXT', 'null' => TRUE),
			'requires_note_on_complete' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0),
			'completed_at' => array('type' => 'DATETIME', 'null' => TRUE),
			'verified_by_employee_id' => array('type' => 'INT', 'constraint' => 10, 'null' => TRUE),
			'verified_by_name' => array('type' => 'VARCHAR', 'constraint' => 255, 'null' => TRUE),
			'verified_at' => array('type' => 'DATETIME', 'null' => TRUE),
			'overdue_at' => array('type' => 'DATETIME', 'null' => TRUE),
			'created_at' => array('type' => 'DATETIME', 'null' => TRUE),
			'updated_at' => array('type' => 'DATETIME', 'null' => TRUE),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->add_key(array('rule_id', 'employee_id', 'assigned_date'));
		$dbforge->create_table('task_assignments', TRUE);
	}
	protected function ensure_task_assignment_items_table($dbforge)
	{
		if ($this->app_db->table_exists('task_assignment_items'))
		{
			return;
		}

		$dbforge->add_field(array(
			'id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE),
			'assignment_id' => array('type' => 'INT', 'constraint' => 10),
			'template_item_id' => array('type' => 'INT', 'constraint' => 10, 'null' => TRUE),
			'name' => array('type' => 'VARCHAR', 'constraint' => 255, 'default' => ''),
			'status' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => 'pending'),
			'completed_at' => array('type' => 'DATETIME', 'null' => TRUE),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->add_key('assignment_id');
		$dbforge->create_table('task_assignment_items', TRUE);
	}

	protected function ensure_task_assignment_notes_table($dbforge)
	{
		if ($this->app_db->table_exists('task_assignment_notes'))
		{
			return;
		}

		$dbforge->add_field(array(
			'id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE),
			'assignment_id' => array('type' => 'INT', 'constraint' => 10),
			'note_type' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => 'manual'),
			'note_text' => array('type' => 'TEXT'),
			'created_by_employee_id' => array('type' => 'INT', 'constraint' => 10, 'null' => TRUE),
			'created_at' => array('type' => 'DATETIME', 'null' => TRUE),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->add_key('assignment_id');
		$dbforge->create_table('task_assignment_notes', TRUE);
	}

	protected function ensure_special_dates_table($dbforge)
	{
		if ($this->app_db->table_exists('special_dates'))
		{
			return;
		}

		$dbforge->add_field(array(
			'id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE),
			'code' => array('type' => 'VARCHAR', 'constraint' => 60, 'default' => ''),
			'category' => array('type' => 'VARCHAR', 'constraint' => 80, 'default' => ''),
			'name' => array('type' => 'VARCHAR', 'constraint' => 255, 'default' => ''),
			'calc_type' => array('type' => 'VARCHAR', 'constraint' => 40, 'default' => 'fixed'),
			'calc_config_json' => array('type' => 'TEXT', 'null' => TRUE),
			'is_active' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 1),
			'sort_order' => array('type' => 'INT', 'constraint' => 10, 'default' => 0),
			'created_at' => array('type' => 'DATETIME', 'null' => TRUE),
			'updated_at' => array('type' => 'DATETIME', 'null' => TRUE),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->add_key('code');
		$dbforge->create_table('special_dates', TRUE);
	}
}
