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

class App_write_model extends CI_Model
{
	protected $app_db;

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

	public function run_migration()
	{
		$dbforge = $this->load->dbforge($this->app_db, TRUE);
		$this->ensure_app_users_table($dbforge);
		$this->ensure_app_settings_table($dbforge);
		$this->ensure_app_user_preferences_table($dbforge);
		$this->ensure_employee_groups_table($dbforge);
		$this->ensure_employee_group_members_table($dbforge);
		$this->ensure_goal_definitions_table($dbforge);
		$this->ensure_goal_definition_groups_table($dbforge);
		$this->ensure_goal_snapshots_table($dbforge);
		$this->ensure_notifications_table($dbforge);
		$this->ensure_attendance_table($dbforge);
		$this->ensure_employee_points_history_table($dbforge);
		$this->seed_default_settings();
		$this->sync_completion_counters_once();
		return array('success' => TRUE, 'message' => 'Tablas base de Visor listas.');
	}

	public function get_app_user($employee_id)
	{
		$this->ensure_app_users_table();
		return $this->app_db->get_where('app_users', array('employee_id' => (int)$employee_id))->row_array();
	}

	public function can_app_user_login($employee_id)
	{
		$user = $this->get_app_user($employee_id);
		if (!$user)
		{
			return TRUE;
		}

		return !isset($user['is_enabled']) || (int)$user['is_enabled'] === 1;
	}

	public function touch_app_user_login($employee_id, $profile = array())
	{
		$this->ensure_app_users_table();

		$employee_id = (int)$employee_id;
		if ($employee_id <= 0)
		{
			return;
		}

		$current = $this->get_app_user($employee_id);
		$is_first_user = !$current && (int)$this->app_db->count_all('app_users') === 0;
		$data = array(
			'employee_id' => $employee_id,
			'username' => isset($profile['username']) ? $profile['username'] : null,
			'full_name' => isset($profile['full_name']) ? $profile['full_name'] : null,
			'is_enabled' => $current && isset($current['is_enabled']) ? (int)$current['is_enabled'] : 1,
			'is_admin' => $current && isset($current['is_admin']) ? (int)$current['is_admin'] : ($is_first_user ? 1 : 0),
			'permissions_json' => $current && isset($current['permissions_json']) ? $current['permissions_json'] : null,
			'admin_permissions_json' => $current && isset($current['admin_permissions_json']) ? $current['admin_permissions_json'] : null,
			'include_in_statistics' => $current && isset($current['include_in_statistics']) ? (int)$current['include_in_statistics'] : 1,
			'current_points' => $current && isset($current['current_points']) ? (int)$current['current_points'] : 0,
			'last_login_at' => date('Y-m-d H:i:s'),
			'updated_at' => date('Y-m-d H:i:s'),
		);

		if ($current)
		{
			$this->app_db->where('employee_id', $employee_id)->update('app_users', $data);
		}
		else
		{
			$this->app_db->insert('app_users', $data);
		}
	}

	public function get_app_user_permissions($employee_id)
	{
		$user = $this->get_app_user($employee_id);
		if (!$user || empty($user['permissions_json']))
		{
			return array();
		}

		$permissions = json_decode($user['permissions_json'], TRUE);
		return is_array($permissions) ? $permissions : array();
	}

	public function get_app_user_admin_permissions($employee_id)
	{
		$user = $this->get_app_user($employee_id);
		if (!$user || empty($user['admin_permissions_json']))
		{
			return array();
		}

		$permissions = json_decode($user['admin_permissions_json'], TRUE);
		return is_array($permissions) ? $permissions : array();
	}

	public function get_all_app_users_index()
	{
		$this->ensure_app_users_table();
		$this->sync_completion_counters_once();
		$rows = $this->app_db->get('app_users')->result_array();
		$index = array();
		foreach ($rows as $row)
		{
			$index[(int)$row['employee_id']] = $row;
		}

		return $index;
	}

	public function get_completion_counters($employee_id)
	{
		$this->ensure_app_users_table();
		$this->sync_completion_counters_once();
		$user = $this->get_app_user($employee_id);
		return array(
			'tasks' => $user && isset($user['completed_tasks_count']) ? (int)$user['completed_tasks_count'] : 0,
			'goals' => $user && isset($user['completed_goals_count']) ? (int)$user['completed_goals_count'] : 0,
		);
	}

	public function increment_task_completed_counter($employee_id, $amount = 1)
	{
		return $this->increment_completion_counter($employee_id, 'completed_tasks_count', $amount);
	}

	public function increment_goal_completed_counter($employee_id, $amount = 1)
	{
		return $this->increment_completion_counter($employee_id, 'completed_goals_count', $amount);
	}

	public function get_employee_goal_completion_history($employee_id, $limit = 100)
	{
		$this->ensure_goal_snapshots_table();
		$this->ensure_goal_definitions_table();
		$employee_id = (int) $employee_id;
		$limit = (int) $limit;
		if ($employee_id <= 0)
		{
			return array();
		}
		if ($limit <= 0)
		{
			$limit = 100;
		}

		$this->app_db->select('goal_snapshots.goal_id, goal_snapshots.current_value, goal_snapshots.target_value, goal_snapshots.period_end, goal_snapshots.created_at, goal_snapshots.updated_at, goal_definitions.name AS goal_name', FALSE);
		$this->app_db->from('goal_snapshots');
		$this->app_db->join('goal_definitions', 'goal_definitions.id = goal_snapshots.goal_id', 'left');
		$this->app_db->where('goal_snapshots.employee_id', $employee_id);
		$this->app_db->where('goal_snapshots.is_completed', 1);
		$this->app_db->order_by('COALESCE(goal_snapshots.updated_at, goal_snapshots.created_at, goal_snapshots.period_end)', 'DESC', FALSE);
		$this->app_db->limit($limit);
		return $this->app_db->get()->result_array();
	}

	public function get_employee_task_completion_history($employee_id, $limit = 100)
	{
		$this->ensure_app_users_table();
		$employee_id = (int) $employee_id;
		$limit = (int) $limit;
		if ($employee_id <= 0)
		{
			return array();
		}
		if ($limit <= 0)
		{
			$limit = 100;
		}
		if (!$this->app_db->table_exists('task_assignments'))
		{
			return array();
		}

		$this->app_db->select('id, title, description, completed_at, assigned_date', FALSE);
		$this->app_db->from('task_assignments');
		$this->app_db->where('employee_id', $employee_id);
		$this->app_db->where('status', 'completed');
		$this->app_db->order_by('COALESCE(completed_at, assigned_date)', 'DESC', FALSE);
		$this->app_db->limit($limit);
		return $this->app_db->get()->result_array();
	}
	public function save_app_user_config($employee_id, $profile = array(), $data = array())
	{
		$this->ensure_app_users_table();

		$employee_id = (int)$employee_id;
		if ($employee_id <= 0)
		{
			return FALSE;
		}

		$current = $this->get_app_user($employee_id);
		$payload = array(
			'employee_id' => $employee_id,
			'username' => isset($profile['username']) ? $profile['username'] : ($current && isset($current['username']) ? $current['username'] : null),
			'full_name' => isset($profile['full_name']) ? $profile['full_name'] : ($current && isset($current['full_name']) ? $current['full_name'] : null),
			'is_enabled' => isset($data['is_enabled']) ? (int)!empty($data['is_enabled']) : ($current && isset($current['is_enabled']) ? (int)$current['is_enabled'] : 1),
			'is_admin' => isset($data['is_admin']) ? (int)!empty($data['is_admin']) : ($current && isset($current['is_admin']) ? (int)$current['is_admin'] : 0),
			'include_in_statistics' => isset($data['include_in_statistics']) ? (int)!empty($data['include_in_statistics']) : ($current && isset($current['include_in_statistics']) ? (int)$current['include_in_statistics'] : 1),
			'permissions_json' => isset($data['permissions']) ? json_encode($data['permissions']) : ($current && isset($current['permissions_json']) ? $current['permissions_json'] : null),
			'admin_permissions_json' => isset($data['admin_permissions']) ? json_encode($data['admin_permissions']) : ($current && isset($current['admin_permissions_json']) ? $current['admin_permissions_json'] : null),
			'current_points' => $current && isset($current['current_points']) ? (int)$current['current_points'] : 0,
			'last_login_at' => $current && isset($current['last_login_at']) ? $current['last_login_at'] : null,
			'updated_at' => date('Y-m-d H:i:s'),
		);

		if ($current)
		{
			return $this->app_db->where('employee_id', $employee_id)->update('app_users', $payload);
		}

		return $this->app_db->insert('app_users', $payload);
	}

	public function get_setting($key, $default = null)
	{
		$this->ensure_app_settings_table();
		$row = $this->app_db->get_where('app_settings', array('setting_key' => (string)$key))->row_array();
		return $row && array_key_exists('setting_value', $row) ? $row['setting_value'] : $default;
	}

	public function create_notification($employee_id, $employee_name, $message)
	{
		$this->ensure_notifications_table();
		$payload = array(
			'message' => (string)$message,
			'created_by_employee_id' => (int)$employee_id,
			'created_by_name' => (string)$employee_name,
			'created_at' => date('Y-m-d H:i:s'),
			'is_reviewed' => 0,
			'reviewed_at' => null,
			'reviewed_by_employee_id' => null,
			'reviewed_by_name' => null,
		);
		return $this->app_db->insert('app_notifications', $payload);
	}

	public function get_notifications()
	{
		$this->ensure_notifications_table();
		$this->app_db->order_by('created_at', 'DESC');
		$this->app_db->order_by('id', 'DESC');
		return $this->app_db->get('app_notifications')->result_array();
	}

	public function mark_notification_reviewed($notification_id, $reviewer_id, $reviewer_name)
	{
		$this->ensure_notifications_table();
		$payload = array(
			'is_reviewed' => 1,
			'reviewed_at' => date('Y-m-d H:i:s'),
			'reviewed_by_employee_id' => (int)$reviewer_id,
			'reviewed_by_name' => (string)$reviewer_name,
		);
		$this->app_db->where('id', (int)$notification_id);
		return $this->app_db->update('app_notifications', $payload);
	}

	public function get_employee_points_total($employee_id)
	{
		$user = $this->get_app_user((int)$employee_id);
		return $user && isset($user['current_points']) ? (int)$user['current_points'] : 0;
	}

	public function get_employee_points_history($employee_id, $limit = 100)
	{
		$this->ensure_employee_points_history_table();
		$employee_id = (int)$employee_id;
		$limit = (int)$limit;
		if ($employee_id <= 0)
		{
			return array();
		}
		if ($limit <= 0)
		{
			$limit = 100;
		}
		$this->app_db->from('app_employee_points_history');
		$this->app_db->where('employee_id', $employee_id);
		$this->app_db->order_by('created_at', 'DESC');
		$this->app_db->order_by('id', 'DESC');
		$this->app_db->limit($limit);
		return $this->app_db->get()->result_array();
	}

	public function add_employee_points_movement($employee_id, $points_delta, $reason, $created_by_employee_id = null, $created_by_name = null, $source_label = 'Ajuste manual')
	{
		$this->ensure_app_users_table();
		$this->ensure_employee_points_history_table();
		$employee_id = (int)$employee_id;
		$points_delta = (int)$points_delta;
		$reason = trim((string)$reason);
		$source_label = trim((string)$source_label);
		if ($employee_id <= 0 || $points_delta === 0)
		{
			return FALSE;
		}
		$this->ensure_app_user_counter_row($employee_id);
		$current_points = $this->get_employee_points_total($employee_id);
		$balance_after = $current_points + $points_delta;
		$payload = array(
			'employee_id' => $employee_id,
			'points_delta' => $points_delta,
			'balance_after' => $balance_after,
			'reason' => $reason !== '' ? $reason : null,
			'source_label' => $source_label !== '' ? substr($source_label, 0, 120) : 'Ajuste manual',
			'created_by_employee_id' => $created_by_employee_id !== null ? (int)$created_by_employee_id : null,
			'created_by_name' => $created_by_name !== null ? substr((string)$created_by_name, 0, 160) : null,
			'created_at' => date('Y-m-d H:i:s'),
		);
		if (!$this->app_db->insert('app_employee_points_history', $payload))
		{
			return FALSE;
		}
		return $this->app_db->where('employee_id', $employee_id)->update('app_users', array(
			'current_points' => $balance_after,
			'updated_at' => date('Y-m-d H:i:s'),
		));
	}

	public function get_attendance_for_date($date_key)
	{
		$this->ensure_attendance_table();
		$date_key = (string)$date_key;
		if ($date_key === '')
		{
			return array();
		}

		$this->app_db->from('app_attendance');
		$this->app_db->where('attendance_date', $date_key);
		$rows = $this->app_db->get()->result_array();
		$index = array();
		foreach ($rows as $row)
		{
			if (!isset($row['employee_id']))
			{
				continue;
			}
			$index[(int)$row['employee_id']] = $row;
		}

		return $index;
	}

	public function get_attendance_between($date_from, $date_to)
	{
		$this->ensure_attendance_table();
		$date_from = (string)$date_from;
		$date_to = (string)$date_to;
		if ($date_from === '' || $date_to === '')
		{
			return array();
		}

		$this->app_db->from('app_attendance');
		$this->app_db->where('attendance_date >=', $date_from);
		$this->app_db->where('attendance_date <=', $date_to);
		$this->app_db->order_by('attendance_date', 'DESC');
		$this->app_db->order_by('employee_id', 'ASC');
		return $this->app_db->get()->result_array();
	}

	public function get_recent_absence_notes($limit = 30, $date_from = null, $date_to = null)
	{
		$this->ensure_attendance_table();
		$limit = (int)$limit;
		if ($limit <= 0)
		{
			$limit = 30;
		}

		$this->app_db->from('app_attendance');
		$this->app_db->where('is_present', 0);
		$this->app_db->group_start();
		$this->app_db->where('note IS NOT NULL', null, FALSE);
		$this->app_db->where('note !=', '');
		$this->app_db->group_end();
		if ($date_from)
		{
			$this->app_db->where('attendance_date >=', (string)$date_from);
		}
		if ($date_to)
		{
			$this->app_db->where('attendance_date <=', (string)$date_to);
		}
		$this->app_db->order_by('attendance_date', 'DESC');
		$this->app_db->order_by('updated_at', 'DESC');
		$this->app_db->limit($limit);
		return $this->app_db->get()->result_array();
	}

	public function save_attendance($date_key, $employee_id, $is_present, $marked_by = null, $note = null, $marked_by_name = null)
	{
		$this->ensure_attendance_table();
		$date_key = (string)$date_key;
		$employee_id = (int)$employee_id;
		if ($date_key === '' || $employee_id <= 0)
		{
			return FALSE;
		}

		$existing = $this->app_db->get_where('app_attendance', array(
			'attendance_date' => $date_key,
			'employee_id' => $employee_id,
		))->row_array();

		$payload = array(
			'attendance_date' => $date_key,
			'employee_id' => $employee_id,
			'is_present' => $is_present ? 1 : 0,
			'marked_by_employee_id' => $marked_by ? (int)$marked_by : null,
			'marked_by_name' => $marked_by_name !== null ? substr((string)$marked_by_name, 0, 120) : ($existing && isset($existing['marked_by_name']) ? $existing['marked_by_name'] : null),
			'note' => $note !== null ? trim((string)$note) : ($existing && isset($existing['note']) ? $existing['note'] : null),
			'updated_at' => date('Y-m-d H:i:s'),
		);

		if ($existing)
		{
			return $this->app_db->where('id', (int)$existing['id'])->update('app_attendance', $payload);
		}

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

	public function save_setting($key, $value)
	{
		$this->ensure_app_settings_table();
		$key = (string)$key;
		$data = array(
			'setting_key' => $key,
			'setting_value' => (string)$value,
			'updated_at' => date('Y-m-d H:i:s'),
		);

		$exists = $this->app_db->get_where('app_settings', array('setting_key' => $key))->row_array();
		if ($exists)
		{
			return $this->app_db->where('setting_key', $key)->update('app_settings', $data);
		}

		return $this->app_db->insert('app_settings', $data);
	}

	public function get_goal_definitions($active_only = FALSE)
	{
		$this->ensure_goal_definitions_table();
		$this->ensure_goal_definition_groups_table();
		$this->expire_finished_goal_definitions();
		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');
		$definitions = $this->app_db->get('goal_definitions')->result_array();
		return $this->attach_goal_definition_group_ids($definitions);
	}

	public function get_goal_definition($goal_id)
	{
		$this->ensure_goal_definitions_table();
		$this->ensure_goal_definition_groups_table();
		$definition = $this->app_db->get_where('goal_definitions', array('id' => (int)$goal_id))->row_array();
		if (!$definition)
		{
			return null;
		}
		$definitions = $this->attach_goal_definition_group_ids(array($definition));
		return !empty($definitions) ? $definitions[0] : $definition;
	}

	public function get_employee_groups($active_only = FALSE)
	{
		$this->ensure_employee_groups_table();
		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('employee_groups')->result_array();
	}

	public function get_employee_group($group_id)
	{
		$this->ensure_employee_groups_table();
		return $this->app_db->get_where('employee_groups', array('id' => (int)$group_id))->row_array();
	}

	public function get_employee_group_members($group_id)
	{
		$this->ensure_employee_group_members_table();
		$this->app_db->select('employee_id');
		$this->app_db->from('employee_group_members');
		$this->app_db->where('group_id', (int)$group_id);
		$rows = $this->app_db->get()->result_array();
		return array_map('intval', array_column($rows, 'employee_id'));
	}

	public function get_employee_group_members_index()
	{
		$this->ensure_employee_group_members_table();
		$rows = $this->app_db->get('employee_group_members')->result_array();
		$index = array();
		foreach ($rows as $row)
		{
			$group_id = isset($row['group_id']) ? (int)$row['group_id'] : 0;
			$employee_id = isset($row['employee_id']) ? (int)$row['employee_id'] : 0;
			if ($group_id <= 0 || $employee_id <= 0)
			{
				continue;
			}
			if (!isset($index[$group_id]))
			{
				$index[$group_id] = array();
			}
			$index[$group_id][] = $employee_id;
		}
		return $index;
	}

	public function save_employee_group($data, $group_id = null)
	{
		$this->ensure_employee_groups_table();
		$group_id = $group_id !== null ? (int)$group_id : null;
		$current = $group_id ? $this->get_employee_group($group_id) : null;
		$payload = array(
			'name' => isset($data['name']) ? trim((string)$data['name']) : ($current ? $current['name'] : ''),
			'is_active' => isset($data['is_active']) ? (int)!empty($data['is_active']) : ($current ? (int)$current['is_active'] : 1),
			'color' => isset($data['color']) ? substr((string)$data['color'], 0, 20) : ($current && isset($current['color']) ? $current['color'] : '#4d74c3'),
			'sort_order' => isset($data['sort_order']) ? (int)$data['sort_order'] : ($current ? (int)$current['sort_order'] : 0),
			'updated_at' => date('Y-m-d H:i:s'),
		);

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

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

	public function replace_employee_group_members($group_id, $employee_ids)
	{
		$this->ensure_employee_group_members_table();
		$group_id = (int)$group_id;
		if ($group_id <= 0)
		{
			return FALSE;
		}

		$clean_ids = array();
		foreach ((array)$employee_ids as $employee_id)
		{
			$employee_id = (int)$employee_id;
			if ($employee_id > 0)
			{
				$clean_ids[$employee_id] = $employee_id;
			}
		}

		$this->app_db->where('group_id', $group_id)->delete('employee_group_members');
		foreach ($clean_ids as $employee_id)
		{
			$this->app_db->insert('employee_group_members', array(
				'group_id' => $group_id,
				'employee_id' => $employee_id,
			));
		}

		return TRUE;
	}

	public function delete_goal_definition($goal_id)
	{
		$this->ensure_goal_definitions_table();
		$this->ensure_goal_definition_groups_table();
		$this->ensure_goal_snapshots_table();

		$goal_id = (int)$goal_id;
		if ($goal_id <= 0)
		{
			return FALSE;
		}

		$goal = $this->get_goal_definition($goal_id);
		if (!$goal)
		{
			return FALSE;
		}

		return $this->app_db
			->where('id', $goal_id)
			->update('goal_definitions', array(
				'is_active' => 0,
				'updated_at' => date('Y-m-d H:i:s'),
			));
	}

	public function save_goal_definition($data, $goal_id = null)
	{
		$this->ensure_goal_definitions_table();
		$this->ensure_goal_definition_groups_table();
		$goal_id = $goal_id !== null ? (int)$goal_id : null;
		$current = $goal_id ? $this->get_goal_definition($goal_id) : null;
		$period_type = isset($data['period_type']) ? trim((string)$data['period_type']) : ($current ? $current['period_type'] : 'daily');
		$is_active = isset($data['is_active']) ? (int)!empty($data['is_active']) : ($current ? (int)$current['is_active'] : 1);
		$run_once = isset($data['run_once']) ? (int)!empty($data['run_once']) : ($current && isset($current['run_once']) ? (int)$current['run_once'] : 0);
		$run_once_mode = $run_once ? (isset($data['run_once_mode']) ? trim((string)$data['run_once_mode']) : ($current && !empty($current['run_once_mode']) ? $current['run_once_mode'] : 'period')) : null;
		$custom_period_start_date = !empty($data['custom_period_start_date']) ? trim((string)$data['custom_period_start_date']) : ($current && !empty($current['custom_period_start_date']) ? $current['custom_period_start_date'] : null);
		$custom_period_end_date = !empty($data['custom_period_end_date']) ? trim((string)$data['custom_period_end_date']) : ($current && !empty($current['custom_period_end_date']) ? $current['custom_period_end_date'] : null);
		$run_once_start_date = null;
		$run_once_end_date = null;
		$active_period_key = null;

		if ($is_active && $run_once)
		{
			if ($run_once_mode === 'custom')
			{
				$run_once_start_date = !empty($data['run_once_start_date']) ? trim((string)$data['run_once_start_date']) : ($current && !empty($current['run_once_start_date']) ? $current['run_once_start_date'] : null);
				$run_once_end_date = !empty($data['run_once_end_date']) ? trim((string)$data['run_once_end_date']) : ($current && !empty($current['run_once_end_date']) ? $current['run_once_end_date'] : null);
			}
			else
			{
				$run_once_mode = 'period';
				$active_period_key = $this->build_goal_period_key($period_type);
			}
		}

		$payload = array(
			'name' => isset($data['name']) ? trim((string)$data['name']) : ($current ? $current['name'] : ''),
			'is_active' => $is_active,
			'period_type' => $period_type,
			'custom_period_start_date' => $period_type === 'custom' ? $custom_period_start_date : null,
			'custom_period_end_date' => $period_type === 'custom' ? $custom_period_end_date : null,
			'scope_type' => isset($data['scope_type']) ? trim((string)$data['scope_type']) : ($current ? $current['scope_type'] : 'general'),
			'scope_id' => array_key_exists('scope_id', $data) && $data['scope_id'] !== '' && $data['scope_id'] !== null ? (int)$data['scope_id'] : ($current && isset($current['scope_id']) ? (int)$current['scope_id'] : null),
			'metric_type' => isset($data['metric_type']) ? trim((string)$data['metric_type']) : ($current ? $current['metric_type'] : 'amount'),
			'goal_method' => isset($data['goal_method']) ? trim((string)$data['goal_method']) : ($current ? $current['goal_method'] : 'fixed'),
			'goal_amount' => isset($data['goal_amount']) ? (float)$data['goal_amount'] : ($current ? (float)$current['goal_amount'] : 0),
			'goal_percent' => isset($data['goal_percent']) ? (float)$data['goal_percent'] : ($current ? (float)$current['goal_percent'] : 0),
			'display_mode' => isset($data['display_mode']) ? trim((string)$data['display_mode']) : ($current ? $current['display_mode'] : 'bar'),
			'employee_mode' => isset($data['employee_mode']) ? trim((string)$data['employee_mode']) : ($current ? $current['employee_mode'] : 'global'),
			'employee_id' => array_key_exists('employee_id', $data) && $data['employee_id'] !== '' && $data['employee_id'] !== null ? (int)$data['employee_id'] : ($current && isset($current['employee_id']) ? (int)$current['employee_id'] : null),
			'group_id' => array_key_exists('group_id', $data) && $data['group_id'] !== '' && $data['group_id'] !== null ? (int)$data['group_id'] : ($current && isset($current['group_id']) ? (int)$current['group_id'] : null),
			'target_scope' => isset($data['target_scope']) ? trim((string)$data['target_scope']) : ($current && isset($current['target_scope']) ? $current['target_scope'] : 'individual'),
			'sort_order' => isset($data['sort_order']) ? (int)$data['sort_order'] : ($current ? (int)$current['sort_order'] : 0),
			'run_once' => $run_once,
			'run_once_mode' => $run_once_mode,
			'run_once_start_date' => $run_once_start_date,
			'run_once_end_date' => $run_once_end_date,
			'active_period_key' => $active_period_key,
			'updated_at' => date('Y-m-d H:i:s'),
		);

		if ($current)
		{
			$updated = $this->app_db->where('id', $goal_id)->update('goal_definitions', $payload);
			if ($updated)
			{
				$this->delete_goal_snapshots_by_goal($goal_id);
			}
			return $updated ? $goal_id : FALSE;
		}

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


	public function delete_goal_snapshots_by_goal($goal_id)
	{
		$this->ensure_goal_snapshots_table();
		$goal_id = (int)$goal_id;
		if ($goal_id <= 0)
		{
			return FALSE;
		}

		$this->app_db->where('goal_id', $goal_id);
		return $this->app_db->delete('goal_snapshots');
	}

	public function get_goal_definition_group_ids($goal_id)
	{
		$this->ensure_goal_definition_groups_table();
		$this->app_db->select('group_id');
		$this->app_db->from('goal_definition_groups');
		$this->app_db->where('goal_id', (int)$goal_id);
		$rows = $this->app_db->get()->result_array();
		$group_ids = array();
		foreach ($rows as $row)
		{
			$group_id = isset($row['group_id']) ? (int)$row['group_id'] : 0;
			if ($group_id > 0)
			{
				$group_ids[$group_id] = $group_id;
			}
		}
		return array_values($group_ids);
	}

	public function get_goal_definition_groups_index()
	{
		$this->ensure_goal_definition_groups_table();
		$rows = $this->app_db->get('goal_definition_groups')->result_array();
		$index = array();
		foreach ($rows as $row)
		{
			$goal_id = isset($row['goal_id']) ? (int)$row['goal_id'] : 0;
			$group_id = isset($row['group_id']) ? (int)$row['group_id'] : 0;
			if ($goal_id <= 0 || $group_id <= 0)
			{
				continue;
			}
			if (!isset($index[$goal_id]))
			{
				$index[$goal_id] = array();
			}
			$index[$goal_id][$group_id] = $group_id;
		}

		foreach ($index as &$group_ids)
		{
			$group_ids = array_values($group_ids);
		}
		unset($group_ids);

		return $index;
	}

	public function get_goal_snapshots_index_for_employee($employee_id, $period_start, $period_end)
	{
		$this->ensure_goal_snapshots_table();
		$this->app_db->from('goal_snapshots');
		$this->app_db->where('employee_id', (int)$employee_id);
		$this->app_db->where('period_start >=', (string)$period_start);
		$this->app_db->where('period_end <=', (string)$period_end);
		$rows = $this->app_db->get()->result_array();
		$index = array();
		foreach ($rows as $row)
		{
			if (empty($row['goal_id']) || empty($row['period_start']) || empty($row['period_end']))
			{
				continue;
			}
			$key = implode('|', array((int)$row['goal_id'], (string)$row['period_start'], (string)$row['period_end']));
			$index[$key] = $row;
		}

		return $index;
	}
	public function get_goal_snapshots_index_for_employees($employee_ids, $period_start, $period_end)
	{
		$this->ensure_goal_snapshots_table();
		$employee_ids = array_values(array_filter(array_map('intval', (array)$employee_ids), function ($employee_id) {
			return $employee_id > 0;
		}));
		if (empty($employee_ids))
		{
			return array();
		}

		$this->app_db->from('goal_snapshots');
		$this->app_db->where_in('employee_id', $employee_ids);
		$this->app_db->where('period_start >=', (string)$period_start);
		$this->app_db->where('period_end <=', (string)$period_end);
		$rows = $this->app_db->get()->result_array();
		$index = array();
		foreach ($rows as $row)
		{
			$employee_id = isset($row['employee_id']) ? (int)$row['employee_id'] : 0;
			if ($employee_id <= 0 || empty($row['goal_id']) || empty($row['period_start']) || empty($row['period_end']))
			{
				continue;
			}
			if (!isset($index[$employee_id]))
			{
				$index[$employee_id] = array();
			}
			$key = implode('|', array((int)$row['goal_id'], (string)$row['period_start'], (string)$row['period_end']));
			$index[$employee_id][$key] = $row;
		}

		return $index;
	}

	public function save_goal_snapshot($data)
	{
		$this->ensure_goal_snapshots_table();
		$goal_id = isset($data['goal_id']) ? (int)$data['goal_id'] : 0;
		$employee_id = isset($data['employee_id']) ? (int)$data['employee_id'] : 0;
		$period_start = isset($data['period_start']) ? (string)$data['period_start'] : '';
		$period_end = isset($data['period_end']) ? (string)$data['period_end'] : '';
		if ($goal_id <= 0 || $employee_id <= 0 || $period_start === '' || $period_end === '')
		{
			return FALSE;
		}

		$payload = array(
			'goal_id' => $goal_id,
			'employee_id' => $employee_id,
			'period_type' => isset($data['period_type']) ? (string)$data['period_type'] : '',
			'period_start' => $period_start,
			'period_end' => $period_end,
			'current_value' => isset($data['current_value']) ? (float)$data['current_value'] : 0.0,
			'target_value' => isset($data['target_value']) ? (float)$data['target_value'] : 0.0,
			'progress_ratio' => isset($data['progress_ratio']) ? (float)$data['progress_ratio'] : 0.0,
			'is_completed' => !empty($data['is_completed']) ? 1 : 0,
			'rank_position' => isset($data['rank_position']) ? (int)$data['rank_position'] : 0,
			'updated_at' => date('Y-m-d H:i:s'),
		);

		$existing = $this->app_db->get_where('goal_snapshots', array(
			'goal_id' => $goal_id,
			'employee_id' => $employee_id,
			'period_start' => $period_start,
			'period_end' => $period_end,
		))->row_array();

		if ($existing)
		{
			$this->app_db->where('id', (int)$existing['id'])->update('goal_snapshots', $payload);
			if (empty($existing['is_completed']) && !empty($payload['is_completed']))
			{
				$this->increment_goal_completed_counter($employee_id, 1);
			}
			return (int)$existing['id'];
		}

		$payload['created_at'] = date('Y-m-d H:i:s');
		$inserted = $this->app_db->insert('goal_snapshots', $payload);
		if ($inserted && !empty($payload['is_completed']))
		{
			$this->increment_goal_completed_counter($employee_id, 1);
		}
		return $inserted ? (int)$this->app_db->insert_id() : FALSE;
	}

	public function replace_goal_definition_groups($goal_id, $group_ids)
	{
		$this->ensure_goal_definition_groups_table();
		$goal_id = (int)$goal_id;
		if ($goal_id <= 0)
		{
			return FALSE;
		}

		$clean_ids = array();
		foreach ((array)$group_ids as $group_id)
		{
			$group_id = (int)$group_id;
			if ($group_id > 0)
			{
				$clean_ids[$group_id] = $group_id;
			}
		}

		$this->app_db->where('goal_id', $goal_id)->delete('goal_definition_groups');
		foreach ($clean_ids as $group_id)
		{
			$this->app_db->insert('goal_definition_groups', array(
				'goal_id' => $goal_id,
				'group_id' => $group_id,
			));
		}

		return TRUE;
	}

	public function expire_finished_goal_definitions()
	{
		$this->ensure_goal_definitions_table();
		$this->app_db->where('is_active', 1);
		$rows = $this->app_db->get('goal_definitions')->result_array();
		if (empty($rows))
		{
			return;
		}

		foreach ($rows as $row)
		{
			if (empty($row['run_once']))
			{
				continue;
			}

			$should_expire = FALSE;
			$mode = isset($row['run_once_mode']) ? (string)$row['run_once_mode'] : 'period';
			if ($mode === 'custom')
			{
				$today = date('Y-m-d');
				$end_date = isset($row['run_once_end_date']) ? (string)$row['run_once_end_date'] : '';
				if ($end_date !== '' && $today > $end_date)
				{
					$should_expire = TRUE;
				}
			}
			else
			{
				$current_key = $this->build_goal_period_key(isset($row['period_type']) ? $row['period_type'] : 'daily');
				$active_key = isset($row['active_period_key']) ? (string)$row['active_period_key'] : '';
				if ($active_key !== '' && $active_key !== $current_key)
				{
					$should_expire = TRUE;
				}
			}

			if ($should_expire)
			{
				$this->app_db->where('id', (int)$row['id'])->update('goal_definitions', array(
					'is_active' => 0,
					'active_period_key' => null,
					'updated_at' => date('Y-m-d H:i:s'),
				));
			}
		}
	}

	public function get_user_preferences($employee_id)
	{
		$this->ensure_app_user_preferences_table();
		$row = $this->app_db->get_where('app_user_preferences', array('employee_id' => (int)$employee_id))->row_array();
		if ($row)
		{
			return $row;
		}

		return $this->get_default_user_preferences($employee_id);
	}

	public function get_default_user_preferences($employee_id = 0)
	{
		return array(
			'employee_id' => (int)$employee_id,
			'theme_color' => '#4d74c3',
			'background_color' => '#f6f7f9',
			'text_color' => '#ffffff',
			'button_color' => '#5f8fa8',
			'price_divisa_size' => 4.10,
			'price_bs_size' => 1.45,
			'application_text_size' => 1.00,
			'application_text_color' => '#183247',
			'application_button_size' => 1.12,
			'application_button_color' => '#4d74c3',
			'application_menu_button_color' => '#4d74c3',
			'application_menu_active_color' => '#355ca8',
			'application_background_color' => '#f6f7f9',
			'application_box_color' => '#ffffff',
			'visor_product_name_size' => 1.05,
			'visor_product_name_color' => '#183247',
			'visor_price_divisa_size' => 4.10,
			'visor_price_divisa_color' => '#4d74c3',
			'visor_price_bs_size' => 1.45,
			'visor_price_bs_color' => '#617386',
			'visor_text_size' => 1.00,
			'visor_text_color' => '#617386',
			'visor_button_size' => 1.00,
			'visor_button_color' => '#5f8fa8',
			'visor_background_color' => '#f6f7f9',
			'visor_box_color' => '#eef3f8',
			'updated_at' => null,
		);
	}

	public function reset_user_preferences($employee_id, $keys = array())
	{
		$employee_id = (int)$employee_id;
		if ($employee_id <= 0)
		{
			return FALSE;
		}

		$defaults = $this->get_default_user_preferences($employee_id);
		if (empty($keys))
		{
			return $this->save_user_preferences($employee_id, $defaults);
		}

		$payload = array();
		foreach ($keys as $key)
		{
			if (array_key_exists($key, $defaults))
			{
				$payload[$key] = $defaults[$key];
			}
		}

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

		return $this->save_user_preferences($employee_id, $payload);
	}

	public function save_user_preferences($employee_id, $data)
	{
		$this->ensure_app_user_preferences_table();

		$employee_id = (int)$employee_id;
		if ($employee_id <= 0)
		{
			return FALSE;
		}

		$current = $this->get_user_preferences($employee_id);
		$payload = array(
			'employee_id' => $employee_id,
			'theme_color' => isset($data['theme_color']) ? substr((string)$data['theme_color'], 0, 20) : $current['theme_color'],
			'background_color' => isset($data['background_color']) ? substr((string)$data['background_color'], 0, 20) : $current['background_color'],
			'text_color' => isset($data['text_color']) ? substr((string)$data['text_color'], 0, 20) : $current['text_color'],
			'button_color' => isset($data['button_color']) ? substr((string)$data['button_color'], 0, 20) : $current['button_color'],
			'price_divisa_size' => isset($data['price_divisa_size']) ? (float)$data['price_divisa_size'] : (float)$current['price_divisa_size'],
			'price_bs_size' => isset($data['price_bs_size']) ? (float)$data['price_bs_size'] : (float)$current['price_bs_size'],
			'application_text_size' => isset($data['application_text_size']) ? (float)$data['application_text_size'] : (float)$current['application_text_size'],
			'application_text_color' => isset($data['application_text_color']) ? substr((string)$data['application_text_color'], 0, 20) : $current['application_text_color'],
			'application_button_size' => isset($data['application_button_size']) ? (float)$data['application_button_size'] : (float)$current['application_button_size'],
			'application_button_color' => isset($data['application_button_color']) ? substr((string)$data['application_button_color'], 0, 20) : $current['application_button_color'],
			'application_menu_button_color' => isset($data['application_menu_button_color']) ? substr((string)$data['application_menu_button_color'], 0, 20) : $current['application_menu_button_color'],
			'application_menu_active_color' => isset($data['application_menu_active_color']) ? substr((string)$data['application_menu_active_color'], 0, 20) : $current['application_menu_active_color'],
			'application_background_color' => isset($data['application_background_color']) ? substr((string)$data['application_background_color'], 0, 20) : $current['application_background_color'],
			'application_box_color' => isset($data['application_box_color']) ? substr((string)$data['application_box_color'], 0, 20) : $current['application_box_color'],
			'visor_product_name_size' => isset($data['visor_product_name_size']) ? (float)$data['visor_product_name_size'] : (float)$current['visor_product_name_size'],
			'visor_product_name_color' => isset($data['visor_product_name_color']) ? substr((string)$data['visor_product_name_color'], 0, 20) : $current['visor_product_name_color'],
			'visor_price_divisa_size' => isset($data['visor_price_divisa_size']) ? (float)$data['visor_price_divisa_size'] : (float)$current['visor_price_divisa_size'],
			'visor_price_divisa_color' => isset($data['visor_price_divisa_color']) ? substr((string)$data['visor_price_divisa_color'], 0, 20) : $current['visor_price_divisa_color'],
			'visor_price_bs_size' => isset($data['visor_price_bs_size']) ? (float)$data['visor_price_bs_size'] : (float)$current['visor_price_bs_size'],
			'visor_price_bs_color' => isset($data['visor_price_bs_color']) ? substr((string)$data['visor_price_bs_color'], 0, 20) : $current['visor_price_bs_color'],
			'visor_text_size' => isset($data['visor_text_size']) ? (float)$data['visor_text_size'] : (float)$current['visor_text_size'],
			'visor_text_color' => isset($data['visor_text_color']) ? substr((string)$data['visor_text_color'], 0, 20) : $current['visor_text_color'],
			'visor_button_size' => isset($data['visor_button_size']) ? (float)$data['visor_button_size'] : (float)$current['visor_button_size'],
			'visor_button_color' => isset($data['visor_button_color']) ? substr((string)$data['visor_button_color'], 0, 20) : $current['visor_button_color'],
			'visor_background_color' => isset($data['visor_background_color']) ? substr((string)$data['visor_background_color'], 0, 20) : $current['visor_background_color'],
			'visor_box_color' => isset($data['visor_box_color']) ? substr((string)$data['visor_box_color'], 0, 20) : $current['visor_box_color'],
			'updated_at' => date('Y-m-d H:i:s'),
		);

		$existing_row = $this->app_db->get_where('app_user_preferences', array('employee_id' => $employee_id))->row_array();
		if ($existing_row)
		{
			return $this->app_db->where('employee_id', $employee_id)->update('app_user_preferences', $payload);
		}

		return $this->app_db->insert('app_user_preferences', $payload);
	}

	private function seed_default_settings()
	{
		$defaults = array(
			'app_name' => 'Visor',
			'discount_percent' => '0',
			'discount_percent_divisa' => '0',
			'discount_percent_bs' => '0',
			'goal_global_daily_enabled' => '0',
			'goal_global_monthly_enabled' => '0',
			'goal_global_daily_amount' => '0',
			'goal_global_monthly_amount' => '0',
			'sales_daily_limit' => '3',
			'sales_messages' => '[]',
			'sales_messages_no_sales' => '[]',
			'sales_messages_with_sales' => '[]',
			'company_name' => 'Nombre Compania',
		);

		foreach ($defaults as $key => $value)
		{
			if ($this->get_setting($key, null) === null)
			{
				$this->app_db->insert('app_settings', array(
					'setting_key' => $key,
					'setting_value' => $value,
					'updated_at' => date('Y-m-d H:i:s'),
				));
			}
		}
	}

	private function ensure_app_users_table($dbforge = null)
	{
		if ($this->app_db->table_exists('app_users'))
		{
			$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
			$this->ensure_app_users_column($dbforge, 'has_custom_goals', array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0, 'after' => 'permissions_json'));
			$this->ensure_app_users_column($dbforge, 'goal_daily_enabled', array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0, 'after' => 'has_custom_goals'));
			$this->ensure_app_users_column($dbforge, 'goal_monthly_enabled', array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0, 'after' => 'goal_daily_enabled'));
			$this->ensure_app_users_column($dbforge, 'goal_daily_amount', array('type' => 'DECIMAL', 'constraint' => '12,2', 'default' => 0, 'after' => 'permissions_json'));
			$this->ensure_app_users_column($dbforge, 'goal_monthly_amount', array('type' => 'DECIMAL', 'constraint' => '12,2', 'default' => 0, 'after' => 'goal_daily_amount'));
			$this->ensure_app_users_column($dbforge, 'completed_tasks_count', array('type' => 'INT', 'constraint' => 10, 'default' => 0, 'after' => 'goal_monthly_amount'));
			$this->ensure_app_users_column($dbforge, 'completed_goals_count', array('type' => 'INT', 'constraint' => 10, 'default' => 0, 'after' => 'completed_tasks_count'));
			$this->ensure_app_users_column($dbforge, 'include_in_statistics', array('type' => 'TINYINT', 'constraint' => 1, 'default' => 1, 'after' => 'is_admin'));
			$this->ensure_app_users_column($dbforge, 'admin_permissions_json', array('type' => 'LONGTEXT', 'null' => TRUE, 'after' => 'permissions_json'));
			$this->ensure_app_users_column($dbforge, 'current_points', array('type' => 'INT', 'constraint' => 11, 'default' => 0, 'after' => 'completed_goals_count'));
			return;
		}

		$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
		$dbforge->add_field(array(
			'employee_id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE),
			'username' => array('type' => 'VARCHAR', 'constraint' => 255, 'null' => TRUE),
			'full_name' => array('type' => 'VARCHAR', 'constraint' => 255, 'null' => TRUE),
			'is_enabled' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 1),
			'is_admin' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0),
			'include_in_statistics' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 1),
			'permissions_json' => array('type' => 'LONGTEXT', 'null' => TRUE),
			'admin_permissions_json' => array('type' => 'LONGTEXT', 'null' => TRUE),
			'has_custom_goals' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0),
			'goal_daily_enabled' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0),
			'goal_monthly_enabled' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0),
			'goal_daily_amount' => array('type' => 'DECIMAL', 'constraint' => '12,2', 'default' => 0),
			'goal_monthly_amount' => array('type' => 'DECIMAL', 'constraint' => '12,2', 'default' => 0),
			'completed_tasks_count' => array('type' => 'INT', 'constraint' => 10, 'default' => 0),
			'completed_goals_count' => array('type' => 'INT', 'constraint' => 10, 'default' => 0),
			'current_points' => array('type' => 'INT', 'constraint' => 11, 'default' => 0),
			'last_login_at' => array('type' => 'DATETIME', 'null' => TRUE),
			'updated_at' => array('type' => 'TIMESTAMP', 'null' => TRUE),
		));
		$dbforge->add_key('employee_id', TRUE);
		$dbforge->create_table('app_users', TRUE);
	}

	private function increment_completion_counter($employee_id, $column, $amount = 1)
	{
		$this->ensure_app_users_table();
		$employee_id = (int)$employee_id;
		$amount = (int)$amount;
		if ($employee_id <= 0 || $amount <= 0)
		{
			return FALSE;
		}

		$this->ensure_app_user_counter_row($employee_id);
		$this->app_db->set($column, $column.' + '.$amount, FALSE);
		$this->app_db->set('updated_at', date('Y-m-d H:i:s'));
		return $this->app_db->where('employee_id', $employee_id)->update('app_users');
	}

	private function ensure_app_user_counter_row($employee_id)
	{
		$employee_id = (int)$employee_id;
		if ($employee_id <= 0)
		{
			return FALSE;
		}

		$current = $this->get_app_user($employee_id);
		if ($current)
		{
			return TRUE;
		}

		return $this->app_db->insert('app_users', array(
			'employee_id' => $employee_id,
			'is_enabled' => 1,
			'is_admin' => 0,
			'include_in_statistics' => 1,
			'current_points' => 0,
			'completed_tasks_count' => 0,
			'completed_goals_count' => 0,
			'updated_at' => date('Y-m-d H:i:s'),
		));
	}

	private function sync_completion_counters_once()
	{
		$this->ensure_app_users_table();
		$this->ensure_app_settings_table();
		if ((string)$this->get_setting('completion_counters_seeded', '0') === '1')
		{
			return;
		}

		$this->app_db->set('completed_tasks_count', 0)->update('app_users');
		$this->app_db->set('completed_goals_count', 0)->update('app_users');

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

		$goal_counts = array();
		if ($this->app_db->table_exists('goal_snapshots'))
		{
			$this->app_db->select('employee_id, COUNT(*) AS total');
			$this->app_db->from('goal_snapshots');
			$this->app_db->where('is_completed', 1);
			$this->app_db->group_by('employee_id');
			$rows = $this->app_db->get()->result_array();
			foreach ($rows as $row)
			{
				$employee_id = isset($row['employee_id']) ? (int)$row['employee_id'] : 0;
				if ($employee_id > 0)
				{
					$goal_counts[$employee_id] = isset($row['total']) ? (int)$row['total'] : 0;
				}
			}
		}

		$employee_ids = array_unique(array_merge(array_keys($task_counts), array_keys($goal_counts)));
		foreach ($employee_ids as $employee_id)
		{
			$employee_id = (int)$employee_id;
			if ($employee_id <= 0)
			{
				continue;
			}

			$this->ensure_app_user_counter_row($employee_id);
			$this->app_db->where('employee_id', $employee_id)->update('app_users', array(
				'completed_tasks_count' => isset($task_counts[$employee_id]) ? (int)$task_counts[$employee_id] : 0,
				'completed_goals_count' => isset($goal_counts[$employee_id]) ? (int)$goal_counts[$employee_id] : 0,
				'updated_at' => date('Y-m-d H:i:s'),
			));
		}

		$this->save_setting('completion_counters_seeded', '1');
	}

	private function ensure_app_settings_table($dbforge = null)
	{
		if ($this->app_db->table_exists('app_settings'))
		{
			return;
		}

		$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
		$dbforge->add_field(array(
			'setting_key' => array('type' => 'VARCHAR', 'constraint' => 100),
			'setting_value' => array('type' => 'TEXT', 'null' => TRUE),
			'updated_at' => array('type' => 'TIMESTAMP', 'null' => TRUE),
		));
		$dbforge->add_key('setting_key', TRUE);
		$dbforge->create_table('app_settings', TRUE);
	}

	private function ensure_app_user_preferences_table($dbforge = null)
	{
		if ($this->app_db->table_exists('app_user_preferences'))
		{
			$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
			$this->ensure_preferences_column($dbforge, 'background_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#f6f7f9', 'after' => 'theme_color'));
			$this->ensure_preferences_column($dbforge, 'application_text_size', array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.00, 'after' => 'price_bs_size'));
			$this->ensure_preferences_column($dbforge, 'application_text_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#183247', 'after' => 'application_text_size'));
			$this->ensure_preferences_column($dbforge, 'application_button_size', array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.12, 'after' => 'application_text_color'));
			$this->ensure_preferences_column($dbforge, 'application_button_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#4d74c3', 'after' => 'application_button_size'));
			$this->ensure_preferences_column($dbforge, 'application_menu_button_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#4d74c3', 'after' => 'application_button_color'));
			$this->ensure_preferences_column($dbforge, 'application_menu_active_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#355ca8', 'after' => 'application_menu_button_color'));
			$this->ensure_preferences_column($dbforge, 'application_background_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#f6f7f9', 'after' => 'application_menu_active_color'));
			$this->ensure_preferences_column($dbforge, 'application_box_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#ffffff', 'after' => 'application_background_color'));
			$this->ensure_preferences_column($dbforge, 'visor_product_name_size', array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.05, 'after' => 'application_box_color'));
			$this->ensure_preferences_column($dbforge, 'visor_product_name_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#183247', 'after' => 'visor_product_name_size'));
			$this->ensure_preferences_column($dbforge, 'visor_price_divisa_size', array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 4.10, 'after' => 'visor_product_name_color'));
			$this->ensure_preferences_column($dbforge, 'visor_price_divisa_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#4d74c3', 'after' => 'visor_price_divisa_size'));
			$this->ensure_preferences_column($dbforge, 'visor_price_bs_size', array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.45, 'after' => 'visor_price_divisa_color'));
			$this->ensure_preferences_column($dbforge, 'visor_price_bs_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#617386', 'after' => 'visor_price_bs_size'));
			$this->ensure_preferences_column($dbforge, 'visor_text_size', array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.00, 'after' => 'visor_price_bs_color'));
			$this->ensure_preferences_column($dbforge, 'visor_text_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#617386', 'after' => 'visor_text_size'));
			$this->ensure_preferences_column($dbforge, 'visor_button_size', array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.00, 'after' => 'visor_text_color'));
			$this->ensure_preferences_column($dbforge, 'visor_button_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#5f8fa8', 'after' => 'visor_button_size'));
			$this->ensure_preferences_column($dbforge, 'visor_background_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#f6f7f9', 'after' => 'visor_button_color'));
			$this->ensure_preferences_column($dbforge, 'visor_box_color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#eef3f8', 'after' => 'visor_background_color'));
			return;
		}

		$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
		$dbforge->add_field(array(
			'employee_id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE),
			'theme_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#4d74c3'),
			'background_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#f6f7f9'),
			'text_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#ffffff'),
			'button_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#5f8fa8'),
			'price_divisa_size' => array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 4.10),
			'price_bs_size' => array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.45),
			'application_text_size' => array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.00),
			'application_text_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#183247'),
			'application_button_size' => array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.12),
			'application_button_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#4d74c3'),
			'application_menu_button_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#4d74c3'),
			'application_menu_active_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#355ca8'),
			'application_background_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#f6f7f9'),
			'application_box_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#ffffff'),
			'visor_product_name_size' => array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.05),
			'visor_product_name_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#183247'),
			'visor_price_divisa_size' => array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 4.10),
			'visor_price_divisa_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#4d74c3'),
			'visor_price_bs_size' => array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.45),
			'visor_price_bs_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#617386'),
			'visor_text_size' => array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.00),
			'visor_text_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#617386'),
			'visor_button_size' => array('type' => 'DECIMAL', 'constraint' => '6,2', 'default' => 1.00),
			'visor_button_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#5f8fa8'),
			'visor_background_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#f6f7f9'),
			'visor_box_color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#eef3f8'),
			'updated_at' => array('type' => 'TIMESTAMP', 'null' => TRUE),
		));
		$dbforge->add_key('employee_id', TRUE);
		$dbforge->create_table('app_user_preferences', TRUE);
	}

	private function ensure_goal_definitions_table($dbforge = null)
	{
		if ($this->app_db->table_exists('goal_definitions'))
		{
			$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
			$this->ensure_goal_definitions_column($dbforge, 'name', array('type' => 'VARCHAR', 'constraint' => 255, 'default' => ''));
			$this->ensure_goal_definitions_column($dbforge, 'is_active', array('type' => 'TINYINT', 'constraint' => 1, 'default' => 1));
			$this->ensure_goal_definitions_column($dbforge, 'period_type', array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'daily'));
			$this->ensure_goal_definitions_column($dbforge, 'custom_period_start_date', array('type' => 'DATE', 'null' => TRUE));
			$this->ensure_goal_definitions_column($dbforge, 'custom_period_end_date', array('type' => 'DATE', 'null' => TRUE));
			$this->ensure_goal_definitions_column($dbforge, 'scope_type', array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'general'));
			$this->ensure_goal_definitions_column($dbforge, 'scope_id', array('type' => 'INT', 'constraint' => 10, 'null' => TRUE));
			$this->ensure_goal_definitions_column($dbforge, 'metric_type', array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'amount'));
			$this->ensure_goal_definitions_column($dbforge, 'goal_method', array('type' => 'VARCHAR', 'constraint' => 50, 'default' => 'fixed'));
			$this->ensure_goal_definitions_column($dbforge, 'goal_amount', array('type' => 'DECIMAL', 'constraint' => '12,2', 'default' => 0));
			$this->ensure_goal_definitions_column($dbforge, 'goal_percent', array('type' => 'DECIMAL', 'constraint' => '12,2', 'default' => 0));
			$this->ensure_goal_definitions_column($dbforge, 'display_mode', array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'bar'));
			$this->ensure_goal_definitions_column($dbforge, 'employee_mode', array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'global'));
			$this->ensure_goal_definitions_column($dbforge, 'employee_id', array('type' => 'INT', 'constraint' => 10, 'null' => TRUE));
			$this->ensure_goal_definitions_column($dbforge, 'group_id', array('type' => 'INT', 'constraint' => 10, 'null' => TRUE));
			$this->ensure_goal_definitions_column($dbforge, 'target_scope', array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'individual'));
			$this->ensure_goal_definitions_column($dbforge, 'sort_order', array('type' => 'INT', 'constraint' => 10, 'default' => 0));
			$this->ensure_goal_definitions_column($dbforge, 'run_once', array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0));
			$this->ensure_goal_definitions_column($dbforge, 'run_once_mode', array('type' => 'VARCHAR', 'constraint' => 20, 'null' => TRUE));
			$this->ensure_goal_definitions_column($dbforge, 'run_once_start_date', array('type' => 'DATE', 'null' => TRUE));
			$this->ensure_goal_definitions_column($dbforge, 'run_once_end_date', array('type' => 'DATE', 'null' => TRUE));
			$this->ensure_goal_definitions_column($dbforge, 'active_period_key', array('type' => 'VARCHAR', 'constraint' => 50, 'null' => TRUE));
			$this->ensure_goal_definitions_column($dbforge, 'created_at', array('type' => 'DATETIME', 'null' => TRUE));
			$this->ensure_goal_definitions_column($dbforge, 'updated_at', array('type' => 'DATETIME', 'null' => TRUE));
			return;
		}

		$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
		$dbforge->add_field(array(
			'id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE),
			'name' => array('type' => 'VARCHAR', 'constraint' => 255, 'default' => ''),
			'is_active' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 1),
			'period_type' => array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'daily'),
			'custom_period_start_date' => array('type' => 'DATE', 'null' => TRUE),
			'custom_period_end_date' => array('type' => 'DATE', 'null' => TRUE),
			'scope_type' => array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'general'),
			'scope_id' => array('type' => 'INT', 'constraint' => 10, 'null' => TRUE),
			'metric_type' => array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'amount'),
			'goal_method' => array('type' => 'VARCHAR', 'constraint' => 50, 'default' => 'fixed'),
			'goal_amount' => array('type' => 'DECIMAL', 'constraint' => '12,2', 'default' => 0),
			'goal_percent' => array('type' => 'DECIMAL', 'constraint' => '12,2', 'default' => 0),
			'display_mode' => array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'bar'),
			'employee_mode' => array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'global'),
			'employee_id' => array('type' => 'INT', 'constraint' => 10, 'null' => TRUE),
			'group_id' => array('type' => 'INT', 'constraint' => 10, 'null' => TRUE),
			'target_scope' => array('type' => 'VARCHAR', 'constraint' => 30, 'default' => 'individual'),
			'sort_order' => array('type' => 'INT', 'constraint' => 10, 'default' => 0),
			'run_once' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0),
			'run_once_mode' => array('type' => 'VARCHAR', 'constraint' => 20, 'null' => TRUE),
			'run_once_start_date' => array('type' => 'DATE', 'null' => TRUE),
			'run_once_end_date' => array('type' => 'DATE', 'null' => TRUE),
			'active_period_key' => array('type' => 'VARCHAR', 'constraint' => 50, 'null' => TRUE),
			'created_at' => array('type' => 'DATETIME', 'null' => TRUE),
			'updated_at' => array('type' => 'DATETIME', 'null' => TRUE),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->create_table('goal_definitions', TRUE);
	}

	private function ensure_goal_definition_groups_table($dbforge = null)
	{
		if ($this->app_db->table_exists('goal_definition_groups'))
		{
			$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
			$this->ensure_goal_definition_groups_column($dbforge, 'goal_id', array('type' => 'INT', 'constraint' => 10));
			$this->ensure_goal_definition_groups_column($dbforge, 'group_id', array('type' => 'INT', 'constraint' => 10));
			return;
		}

		$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
		$dbforge->add_field(array(
			'id' => array(
				'type' => 'INT',
				'constraint' => 10,
				'unsigned' => TRUE,
				'auto_increment' => TRUE,
			),
			'goal_id' => array('type' => 'INT', 'constraint' => 10),
			'group_id' => array('type' => 'INT', 'constraint' => 10),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->create_table('goal_definition_groups', TRUE);
	}

	private function ensure_goal_snapshots_table($dbforge = null)
	{
		if ($this->app_db->table_exists('goal_snapshots'))
		{
			$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
			$this->ensure_goal_snapshots_column($dbforge, 'goal_id', array('type' => 'INT', 'constraint' => 10));
			$this->ensure_goal_snapshots_column($dbforge, 'employee_id', array('type' => 'INT', 'constraint' => 10));
			$this->ensure_goal_snapshots_column($dbforge, 'period_type', array('type' => 'VARCHAR', 'constraint' => 30, 'default' => ''));
			$this->ensure_goal_snapshots_column($dbforge, 'period_start', array('type' => 'DATETIME'));
			$this->ensure_goal_snapshots_column($dbforge, 'period_end', array('type' => 'DATETIME'));
			$this->ensure_goal_snapshots_column($dbforge, 'current_value', array('type' => 'DECIMAL', 'constraint' => '18,4', 'default' => 0));
			$this->ensure_goal_snapshots_column($dbforge, 'target_value', array('type' => 'DECIMAL', 'constraint' => '18,4', 'default' => 0));
			$this->ensure_goal_snapshots_column($dbforge, 'progress_ratio', array('type' => 'DECIMAL', 'constraint' => '8,6', 'default' => 0));
			$this->ensure_goal_snapshots_column($dbforge, 'is_completed', array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0));
			$this->ensure_goal_snapshots_column($dbforge, 'rank_position', array('type' => 'INT', 'constraint' => 10, 'default' => 0));
			$this->ensure_goal_snapshots_column($dbforge, 'created_at', array('type' => 'DATETIME', 'null' => TRUE));
			$this->ensure_goal_snapshots_column($dbforge, 'updated_at', array('type' => 'DATETIME', 'null' => TRUE));
			return;
		}

		$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
		$dbforge->add_field(array(
			'id' => array(
				'type' => 'INT',
				'constraint' => 10,
				'unsigned' => TRUE,
				'auto_increment' => TRUE,
			),
			'goal_id' => array('type' => 'INT', 'constraint' => 10),
			'employee_id' => array('type' => 'INT', 'constraint' => 10),
			'period_type' => array('type' => 'VARCHAR', 'constraint' => 30, 'default' => ''),
			'period_start' => array('type' => 'DATETIME'),
			'period_end' => array('type' => 'DATETIME'),
			'current_value' => array('type' => 'DECIMAL', 'constraint' => '18,4', 'default' => 0),
			'target_value' => array('type' => 'DECIMAL', 'constraint' => '18,4', 'default' => 0),
			'progress_ratio' => array('type' => 'DECIMAL', 'constraint' => '8,6', 'default' => 0),
			'is_completed' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 0),
			'rank_position' => 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('goal_id');
		$dbforge->add_key('employee_id');
		$dbforge->add_key(array('goal_id', 'employee_id', 'period_start', 'period_end'));
		$dbforge->create_table('goal_snapshots', TRUE);
	}

	private function ensure_notifications_table($dbforge = null)
	{
		if ($this->app_db->table_exists('app_notifications'))
		{
			return;
		}

		$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
		$dbforge->add_field(array(
			'id' => array(
				'type' => 'INT',
				'constraint' => 11,
				'unsigned' => TRUE,
				'auto_increment' => TRUE,
			),
			'message' => array(
				'type' => 'TEXT',
				'null' => FALSE,
			),
			'created_by_employee_id' => array(
				'type' => 'INT',
				'constraint' => 11,
				'unsigned' => TRUE,
				'null' => TRUE,
			),
			'created_by_name' => array(
				'type' => 'VARCHAR',
				'constraint' => 160,
				'null' => TRUE,
			),
			'created_at' => array(
				'type' => 'DATETIME',
				'null' => FALSE,
			),
			'is_reviewed' => array(
				'type' => 'TINYINT',
				'constraint' => 1,
				'default' => 0,
			),
			'reviewed_at' => array(
				'type' => 'DATETIME',
				'null' => TRUE,
			),
			'reviewed_by_employee_id' => array(
				'type' => 'INT',
				'constraint' => 11,
				'unsigned' => TRUE,
				'null' => TRUE,
			),
			'reviewed_by_name' => array(
				'type' => 'VARCHAR',
				'constraint' => 160,
				'null' => TRUE,
			),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->add_key('created_at');
		$dbforge->create_table('app_notifications', TRUE);
	}

	private function ensure_attendance_table($dbforge = null)
	{
		if ($this->app_db->table_exists('app_attendance'))
		{
			$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
			if (!$this->app_db->field_exists('note', 'app_attendance'))
			{
				$dbforge->add_column('app_attendance', array(
					'note' => array(
						'type' => 'TEXT',
						'null' => TRUE,
					),
				));
			}
			if (!$this->app_db->field_exists('marked_by_name', 'app_attendance'))
			{
				$dbforge->add_column('app_attendance', array(
					'marked_by_name' => array(
						'type' => 'VARCHAR',
						'constraint' => 120,
						'null' => TRUE,
					),
				));
			}
			return;
		}

		$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
		$dbforge->add_field(array(
			'id' => array(
				'type' => 'INT',
				'constraint' => 11,
				'unsigned' => TRUE,
				'auto_increment' => TRUE,
			),
			'attendance_date' => array(
				'type' => 'DATE',
				'null' => FALSE,
			),
			'employee_id' => array(
				'type' => 'INT',
				'constraint' => 11,
				'unsigned' => TRUE,
				'null' => FALSE,
			),
			'is_present' => array(
				'type' => 'TINYINT',
				'constraint' => 1,
				'default' => 0,
			),
			'marked_by_employee_id' => array(
				'type' => 'INT',
				'constraint' => 11,
				'unsigned' => TRUE,
				'null' => TRUE,
			),
			'marked_by_name' => array(
				'type' => 'VARCHAR',
				'constraint' => 120,
				'null' => TRUE,
			),
			'note' => array(
				'type' => 'TEXT',
				'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('attendance_date', 'employee_id'));
		$dbforge->create_table('app_attendance', TRUE);
	}

	private function ensure_employee_points_history_table($dbforge = null)
	{
		if ($this->app_db->table_exists('app_employee_points_history'))
		{
			return;
		}

		$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
		$dbforge->add_field(array(
			'id' => array(
				'type' => 'INT',
				'constraint' => 11,
				'unsigned' => TRUE,
				'auto_increment' => TRUE,
			),
			'employee_id' => array(
				'type' => 'INT',
				'constraint' => 11,
				'unsigned' => TRUE,
				'null' => FALSE,
			),
			'points_delta' => array(
				'type' => 'INT',
				'constraint' => 11,
				'default' => 0,
			),
			'balance_after' => array(
				'type' => 'INT',
				'constraint' => 11,
				'default' => 0,
			),
			'source_label' => array(
				'type' => 'VARCHAR',
				'constraint' => 120,
				'null' => TRUE,
			),
			'reason' => array(
				'type' => 'TEXT',
				'null' => TRUE,
			),
			'created_by_employee_id' => array(
				'type' => 'INT',
				'constraint' => 11,
				'unsigned' => TRUE,
				'null' => TRUE,
			),
			'created_by_name' => array(
				'type' => 'VARCHAR',
				'constraint' => 160,
				'null' => TRUE,
			),
			'created_at' => array(
				'type' => 'DATETIME',
				'null' => FALSE,
			),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->add_key(array('employee_id', 'created_at'));
		$dbforge->create_table('app_employee_points_history', TRUE);
	}

	private function ensure_goal_snapshots_column($dbforge, $column, $definition)
	{
		if (!$this->app_db->field_exists($column, 'goal_snapshots'))
		{
			$dbforge->add_column('goal_snapshots', array($column => $definition));
		}
	}

	private function ensure_preferences_column($dbforge, $column, $definition)
	{
		if (!$this->app_db->field_exists($column, 'app_user_preferences'))
		{
			$dbforge->add_column('app_user_preferences', array($column => $definition));
		}
	}

	private function ensure_app_users_column($dbforge, $column, $definition)
	{
		if (!$this->app_db->field_exists($column, 'app_users'))
		{
			$dbforge->add_column('app_users', array($column => $definition));
		}
	}

	private function ensure_goal_definitions_column($dbforge, $column, $definition)
	{
		if (!$this->app_db->field_exists($column, 'goal_definitions'))
		{
			$dbforge->add_column('goal_definitions', array($column => $definition));
		}
	}

	private function ensure_employee_groups_table($dbforge = null)
	{
		if ($this->app_db->table_exists('employee_groups'))
		{
			$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
			$this->ensure_employee_groups_column($dbforge, 'name', array('type' => 'VARCHAR', 'constraint' => 255, 'default' => ''));
			$this->ensure_employee_groups_column($dbforge, 'is_active', array('type' => 'TINYINT', 'constraint' => 1, 'default' => 1));
			$this->ensure_employee_groups_column($dbforge, 'color', array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#4d74c3'));
			$this->ensure_employee_groups_column($dbforge, 'sort_order', array('type' => 'INT', 'constraint' => 10, 'default' => 0));
			$this->ensure_employee_groups_column($dbforge, 'created_at', array('type' => 'DATETIME', 'null' => TRUE));
			$this->ensure_employee_groups_column($dbforge, 'updated_at', array('type' => 'DATETIME', 'null' => TRUE));
			return;
		}

		$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
		$dbforge->add_field(array(
			'id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE),
			'name' => array('type' => 'VARCHAR', 'constraint' => 255, 'default' => ''),
			'is_active' => array('type' => 'TINYINT', 'constraint' => 1, 'default' => 1),
			'color' => array('type' => 'VARCHAR', 'constraint' => 20, 'default' => '#4d74c3'),
			'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->create_table('employee_groups', TRUE);
	}

	private function ensure_employee_group_members_table($dbforge = null)
	{
		if ($this->app_db->table_exists('employee_group_members'))
		{
			$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
			$this->ensure_employee_group_members_column($dbforge, 'group_id', array('type' => 'INT', 'constraint' => 10));
			$this->ensure_employee_group_members_column($dbforge, 'employee_id', array('type' => 'INT', 'constraint' => 10));
			return;
		}

		$dbforge = $dbforge ?: $this->load->dbforge($this->app_db, TRUE);
		$dbforge->add_field(array(
			'id' => array('type' => 'INT', 'constraint' => 10, 'unsigned' => TRUE, 'auto_increment' => TRUE),
			'group_id' => array('type' => 'INT', 'constraint' => 10),
			'employee_id' => array('type' => 'INT', 'constraint' => 10),
		));
		$dbforge->add_key('id', TRUE);
		$dbforge->create_table('employee_group_members', TRUE);
	}

	private function ensure_employee_groups_column($dbforge, $column, $definition)
	{
		if (!$this->app_db->field_exists($column, 'employee_groups'))
		{
			$dbforge->add_column('employee_groups', array($column => $definition));
		}
	}

	private function ensure_goal_definition_groups_column($dbforge, $column, $definition)
	{
		if (!$this->app_db->field_exists($column, 'goal_definition_groups'))
		{
			$dbforge->add_column('goal_definition_groups', array($column => $definition));
		}
	}

	private function attach_goal_definition_group_ids($definitions)
	{
		if (empty($definitions))
		{
			return array();
		}

		$groups_index = $this->get_goal_definition_groups_index();
		foreach ($definitions as &$definition)
		{
			$goal_id = isset($definition['id']) ? (int)$definition['id'] : 0;
			$definition['group_ids'] = isset($groups_index[$goal_id]) ? $groups_index[$goal_id] : array();
			if (empty($definition['group_ids']) && !empty($definition['group_id']))
			{
				$definition['group_ids'] = array((int)$definition['group_id']);
			}
		}
		unset($definition);

		return $definitions;
	}

	private function ensure_employee_group_members_column($dbforge, $column, $definition)
	{
		if (!$this->app_db->field_exists($column, 'employee_group_members'))
		{
			$dbforge->add_column('employee_group_members', array($column => $definition));
		}
	}

	private function build_goal_period_key($period_type)
	{
		$timezone = new DateTimeZone('America/Caracas');
		$today = new DateTime('now', $timezone);

		switch ((string)$period_type)
		{
			case 'weekly':
				return 'weekly-'.$today->format('o-\WW');
			case 'biweekly':
				$half = ((int)$today->format('d') <= 15) ? '1' : '2';
				return 'biweekly-'.$today->format('Y-m').'-'.$half;
			case 'monthly':
				return 'monthly-'.$today->format('Y-m');
			case 'daily':
			default:
				return 'daily-'.$today->format('Y-m-d');
		}
	}
}



