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

class Catalog_model extends CI_Model
{
	/** @var CI_DB_query_builder */
	private $db_catalog;

	/** @var CI_DB_query_builder */
	private $db_gala;

	public function __construct()
	{
		parent::__construct();
		$this->db_catalog = $this->load->database('catalogo_rw', TRUE);
		$this->db_gala = $this->load->database('gala_ro', TRUE);
	}

	public function ensure_schema()
	{
		$this->db_catalog->query("
			CREATE TABLE IF NOT EXISTS catalog_links (
				id INT UNSIGNED NOT NULL AUTO_INCREMENT,
				token VARCHAR(100) NOT NULL,
				customer_id INT NOT NULL,
				customer_name VARCHAR(255) NOT NULL,
				tier_id INT NULL,
				employee_id INT NULL,
				location_id INT NULL,
				expires_at DATETIME NOT NULL,
				created_at DATETIME NOT NULL,
				created_by VARCHAR(120) NULL,
				last_opened_at DATETIME NULL,
				status TINYINT(1) NOT NULL DEFAULT 1,
				PRIMARY KEY (id),
				UNIQUE KEY ux_catalog_links_token (token),
				KEY ix_catalog_links_customer_id (customer_id),
				KEY ix_catalog_links_expires_at (expires_at)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		");

		if (!$this->db_catalog->field_exists('employee_id', 'catalog_links'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_links ADD COLUMN employee_id INT NULL AFTER tier_id");
		}

		if (!$this->db_catalog->field_exists('location_id', 'catalog_links'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_links ADD COLUMN location_id INT NULL AFTER employee_id");
		}

		$this->db_catalog->query("
			CREATE TABLE IF NOT EXISTS catalog_orders (
				id INT UNSIGNED NOT NULL AUTO_INCREMENT,
				link_id INT UNSIGNED NOT NULL,
				customer_id INT NOT NULL,
				sale_id INT NULL,
				customer_note TEXT NULL,
				ordered_at DATETIME NOT NULL,
				subtotal DECIMAL(23,10) NOT NULL DEFAULT 0,
				tax DECIMAL(23,10) NOT NULL DEFAULT 0,
				total DECIMAL(23,10) NOT NULL DEFAULT 0,
				status VARCHAR(30) NOT NULL DEFAULT 'submitted',
				PRIMARY KEY (id),
				KEY ix_catalog_orders_link_id (link_id),
				KEY ix_catalog_orders_customer_id (customer_id),
				KEY ix_catalog_orders_sale_id (sale_id)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		");

		$this->db_catalog->query("
			CREATE TABLE IF NOT EXISTS catalog_order_items (
				id INT UNSIGNED NOT NULL AUTO_INCREMENT,
				order_id INT UNSIGNED NOT NULL,
				item_id INT NOT NULL,
				item_name VARCHAR(255) NOT NULL,
				quantity DECIMAL(23,10) NOT NULL DEFAULT 0,
				unit_price DECIMAL(23,10) NOT NULL DEFAULT 0,
				line_total DECIMAL(23,10) NOT NULL DEFAULT 0,
				image_id INT NULL,
				PRIMARY KEY (id),
				KEY ix_catalog_order_items_order_id (order_id),
				KEY ix_catalog_order_items_item_id (item_id)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		");

		$this->db_catalog->query("
			CREATE TABLE IF NOT EXISTS catalog_employee_permissions (
				person_id INT NOT NULL,
				can_access_panel TINYINT(1) NOT NULL DEFAULT 1,
				can_view_dashboard TINYINT(1) NOT NULL DEFAULT 1,
				can_manage_links TINYINT(1) NOT NULL DEFAULT 1,
				can_create_links TINYINT(1) NOT NULL DEFAULT 1,
				can_view_orders TINYINT(1) NOT NULL DEFAULT 1,
				can_manage_inventory TINYINT(1) NOT NULL DEFAULT 1,
				can_manage_catalog TINYINT(1) NOT NULL DEFAULT 1,
				can_download_reports TINYINT(1) NOT NULL DEFAULT 1,
				can_manage_permissions TINYINT(1) NOT NULL DEFAULT 0,
				updated_at DATETIME NOT NULL,
				updated_by INT NULL,
				PRIMARY KEY (person_id)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		");

		if (!$this->db_catalog->field_exists('can_view_dashboard', 'catalog_employee_permissions'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_employee_permissions ADD COLUMN can_view_dashboard TINYINT(1) NOT NULL DEFAULT 1 AFTER can_access_panel");
		}
		if (!$this->db_catalog->field_exists('can_manage_links', 'catalog_employee_permissions'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_employee_permissions ADD COLUMN can_manage_links TINYINT(1) NOT NULL DEFAULT 1 AFTER can_view_dashboard");
		}
		if (!$this->db_catalog->field_exists('can_manage_inventory', 'catalog_employee_permissions'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_employee_permissions ADD COLUMN can_manage_inventory TINYINT(1) NOT NULL DEFAULT 1 AFTER can_view_orders");
		}
		if (!$this->db_catalog->field_exists('can_manage_catalog', 'catalog_employee_permissions'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_employee_permissions ADD COLUMN can_manage_catalog TINYINT(1) NOT NULL DEFAULT 1 AFTER can_manage_inventory");
		}

		$this->db_catalog->query("
			CREATE TABLE IF NOT EXISTS catalog_templates (
				id INT UNSIGNED NOT NULL AUTO_INCREMENT,
				name VARCHAR(120) NOT NULL,
				primary_color VARCHAR(7) NOT NULL DEFAULT '#d62828',
				secondary_color VARCHAR(7) NOT NULL DEFAULT '#003049',
				bg_color VARCHAR(7) NOT NULL DEFAULT '#fcfaf5',
				image_bg_color VARCHAR(7) NOT NULL DEFAULT '#f4f1e6',
				border_color VARCHAR(7) NOT NULL DEFAULT '#e4ddce',
				text_color VARCHAR(7) NOT NULL DEFAULT '#0f172a',
				muted_text_color VARCHAR(7) NOT NULL DEFAULT '#546278',
				title_size_px INT NOT NULL DEFAULT 17,
				desc_size_px INT NOT NULL DEFAULT 13,
				price_size_px INT NOT NULL DEFAULT 24,
				logo_size_px INT NOT NULL DEFAULT 64,
				card_style VARCHAR(20) NOT NULL DEFAULT 'rounded',
				is_active TINYINT(1) NOT NULL DEFAULT 0,
				created_at DATETIME NOT NULL,
				PRIMARY KEY (id),
				KEY ix_catalog_templates_active (is_active)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		");

		if (!$this->db_catalog->field_exists('border_color', 'catalog_templates'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_templates ADD COLUMN border_color VARCHAR(7) NOT NULL DEFAULT '#e4ddce' AFTER bg_color");
		}

		if (!$this->db_catalog->field_exists('image_bg_color', 'catalog_templates'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_templates ADD COLUMN image_bg_color VARCHAR(7) NOT NULL DEFAULT '#f4f1e6' AFTER bg_color");
		}

		if (!$this->db_catalog->field_exists('text_color', 'catalog_templates'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_templates ADD COLUMN text_color VARCHAR(7) NOT NULL DEFAULT '#0f172a' AFTER border_color");
		}

		if (!$this->db_catalog->field_exists('muted_text_color', 'catalog_templates'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_templates ADD COLUMN muted_text_color VARCHAR(7) NOT NULL DEFAULT '#546278' AFTER text_color");
		}

		if (!$this->db_catalog->field_exists('title_size_px', 'catalog_templates'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_templates ADD COLUMN title_size_px INT NOT NULL DEFAULT 17 AFTER muted_text_color");
		}

		if (!$this->db_catalog->field_exists('desc_size_px', 'catalog_templates'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_templates ADD COLUMN desc_size_px INT NOT NULL DEFAULT 13 AFTER title_size_px");
		}

		if (!$this->db_catalog->field_exists('price_size_px', 'catalog_templates'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_templates ADD COLUMN price_size_px INT NOT NULL DEFAULT 24 AFTER desc_size_px");
		}

		if (!$this->db_catalog->field_exists('logo_size_px', 'catalog_templates'))
		{
			$this->db_catalog->query("ALTER TABLE catalog_templates ADD COLUMN logo_size_px INT NOT NULL DEFAULT 64 AFTER price_size_px");
		}

		$total_templates = (int) $this->db_catalog->count_all('catalog_templates');
		if ($total_templates === 0)
		{
			$now = date('Y-m-d H:i:s');
			$this->db_catalog->insert('catalog_templates', array(
				'name' => 'Rojo Clasico',
				'primary_color' => '#d62828',
				'secondary_color' => '#003049',
				'bg_color' => '#fcfaf5',
				'image_bg_color' => '#f4f1e6',
				'border_color' => '#e4ddce',
				'text_color' => '#0f172a',
				'muted_text_color' => '#546278',
				'title_size_px' => 17,
				'desc_size_px' => 13,
				'price_size_px' => 24,
				'logo_size_px' => 64,
				'card_style' => 'rounded',
				'is_active' => 1,
				'created_at' => $now
			));

			$this->db_catalog->insert('catalog_templates', array(
				'name' => 'Verde Menta',
				'primary_color' => '#0f766e',
				'secondary_color' => '#1d3557',
				'bg_color' => '#f0fdfa',
				'image_bg_color' => '#e6f7f2',
				'border_color' => '#cce8df',
				'text_color' => '#102a43',
				'muted_text_color' => '#486581',
				'title_size_px' => 17,
				'desc_size_px' => 13,
				'price_size_px' => 24,
				'logo_size_px' => 64,
				'card_style' => 'soft',
				'is_active' => 0,
				'created_at' => $now
			));

			$this->db_catalog->insert('catalog_templates', array(
				'name' => 'Noche Azul',
				'primary_color' => '#2563eb',
				'secondary_color' => '#0f172a',
				'bg_color' => '#eff6ff',
				'image_bg_color' => '#e7eef9',
				'border_color' => '#bfd0ea',
				'text_color' => '#0f172a',
				'muted_text_color' => '#475569',
				'title_size_px' => 17,
				'desc_size_px' => 13,
				'price_size_px' => 24,
				'logo_size_px' => 64,
				'card_style' => 'sharp',
				'is_active' => 0,
				'created_at' => $now
			));
		}

		$this->db_catalog->query("
			CREATE TABLE IF NOT EXISTS catalog_settings (
				`key` VARCHAR(80) NOT NULL,
				`value` TEXT NOT NULL,
				updated_at DATETIME NOT NULL,
				PRIMARY KEY (`key`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		");

		$this->db_catalog->query("
			CREATE TABLE IF NOT EXISTS catalog_category_sort (
				category_id INT NOT NULL,
				sort_order INT NOT NULL DEFAULT 9999,
				updated_at DATETIME NOT NULL,
				PRIMARY KEY (category_id),
				KEY ix_catalog_category_sort_order (sort_order)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		");

		$this->db_catalog->query("
			CREATE TABLE IF NOT EXISTS catalog_item_image_sort (
				item_id INT NOT NULL,
				image_id INT NOT NULL,
				sort_order INT NOT NULL DEFAULT 9999,
				updated_at DATETIME NOT NULL,
				PRIMARY KEY (item_id, image_id),
				KEY ix_catalog_item_image_sort_order (item_id, sort_order)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		");

		$exists_product_order = $this->db_catalog
			->query("SELECT `key` FROM catalog_settings WHERE `key` = 'product_order' LIMIT 1")
			->row_array();
		if (!$exists_product_order)
		{
			$this->db_catalog->insert('catalog_settings', array(
				'key' => 'product_order',
				'value' => 'name_asc',
				'updated_at' => date('Y-m-d H:i:s')
			));
		}

		$exists_min_visible_stock = $this->db_catalog
			->query("SELECT `key` FROM catalog_settings WHERE `key` = 'min_visible_stock' LIMIT 1")
			->row_array();
		if (!$exists_min_visible_stock)
		{
			$this->db_catalog->insert('catalog_settings', array(
				'key' => 'min_visible_stock',
				'value' => '0',
				'updated_at' => date('Y-m-d H:i:s')
			));
		}

		$exists_thank_you_message = $this->db_catalog
			->query("SELECT `key` FROM catalog_settings WHERE `key` = 'thank_you_message' LIMIT 1")
			->row_array();
		if (!$exists_thank_you_message)
		{
			$this->db_catalog->insert('catalog_settings', array(
				'key' => 'thank_you_message',
				'value' => 'Gracias por su pedido. Pronto nos comunicaremos con usted.',
				'updated_at' => date('Y-m-d H:i:s')
			));
		}

		$exists_catalog_price_tier_id = $this->db_catalog
			->query("SELECT `key` FROM catalog_settings WHERE `key` = 'catalog_price_tier_id' LIMIT 1")
			->row_array();
		if (!$exists_catalog_price_tier_id)
		{
			$this->db_catalog->insert('catalog_settings', array(
				'key' => 'catalog_price_tier_id',
				'value' => '0',
				'updated_at' => date('Y-m-d H:i:s')
			));
		}

		$exists_catalog_announcement = $this->db_catalog
			->query("SELECT `key` FROM catalog_settings WHERE `key` = 'catalog_announcement' LIMIT 1")
			->row_array();
		if (!$exists_catalog_announcement)
		{
			$this->db_catalog->insert('catalog_settings', array(
				'key' => 'catalog_announcement',
				'value' => '',
				'updated_at' => date('Y-m-d H:i:s')
			));
		}

		$exists_disabled_link_message = $this->db_catalog
			->query("SELECT `key` FROM catalog_settings WHERE `key` = 'disabled_link_message' LIMIT 1")
			->row_array();
		if (!$exists_disabled_link_message)
		{
			$this->db_catalog->insert('catalog_settings', array(
				'key' => 'disabled_link_message',
				'value' => 'Este link fue desactivado por la empresa. Si necesitas ayuda, comunicate con nuestro equipo.',
				'updated_at' => date('Y-m-d H:i:s')
			));
		}

		$exists_checkout_terms = $this->db_catalog
			->query("SELECT `key` FROM catalog_settings WHERE `key` = 'checkout_terms' LIMIT 1")
			->row_array();
		if (!$exists_checkout_terms)
		{
			$this->db_catalog->insert('catalog_settings', array(
				'key' => 'checkout_terms',
				'value' => '',
				'updated_at' => date('Y-m-d H:i:s')
			));
		}
	}

	public function get_customers()
	{
		return $this->db_gala
			->select('c.id, c.tier_id, c.company_name, p.first_name, p.last_name, p.email')
			->from('phppos_customers c')
			->join('phppos_people p', 'p.person_id = c.person_id', 'left')
			->where('c.deleted', 0)
			->order_by('c.company_name', 'ASC')
			->get()
			->result_array();
	}

	public function get_employees()
	{
		return $this->db_gala
			->select('e.person_id, e.username, p.first_name, p.last_name')
			->from('phppos_employees e')
			->join('phppos_people p', 'p.person_id = e.person_id', 'left')
			->where('e.deleted', 0)
			->order_by('p.first_name', 'ASC')
			->get()
			->result_array();
	}

	public function get_price_tiers()
	{
		$table_exists = $this->db_gala
			->query("SHOW TABLES LIKE 'phppos_price_tiers'")
			->row_array();
		if (!$table_exists)
		{
			return array();
		}

		$query = $this->db_gala
			->select('id, name')
			->from('phppos_price_tiers');

		if ($this->db_gala->field_exists('deleted', 'phppos_price_tiers'))
		{
			$query->where('deleted', 0);
		}

		return $query
			->order_by('name', 'ASC')
			->get()
			->result_array();
	}

	public function get_permissions_for_employees()
	{
		$sql = "
			SELECT
				e.person_id,
				e.username,
				p.first_name,
				p.last_name,
				COALESCE(cp.can_access_panel,
					CASE
						WHEN e.person_id = 1 THEN 1
						WHEN EXISTS (
							SELECT 1 FROM phppos_permissions pp
							WHERE pp.person_id = e.person_id
							AND pp.module_id IN ('sales', 'reports', 'items', 'config')
						) THEN 1 ELSE 0
					END
				) AS can_access_panel,
				COALESCE(cp.can_create_links,
					CASE
						WHEN e.person_id = 1 THEN 1
						WHEN EXISTS (
							SELECT 1 FROM phppos_permissions pp
							WHERE pp.person_id = e.person_id
							AND pp.module_id IN ('sales', 'config')
						) THEN 1 ELSE 0
					END
				) AS can_create_links,
				COALESCE(cp.can_view_dashboard,
					CASE
						WHEN e.person_id = 1 THEN 1
						WHEN EXISTS (
							SELECT 1 FROM phppos_permissions pp
							WHERE pp.person_id = e.person_id
							AND pp.module_id IN ('sales', 'reports', 'items', 'config')
						) THEN 1 ELSE 0
					END
				) AS can_view_dashboard,
				COALESCE(cp.can_manage_links,
					CASE
						WHEN e.person_id = 1 THEN 1
						WHEN EXISTS (
							SELECT 1 FROM phppos_permissions pp
							WHERE pp.person_id = e.person_id
							AND pp.module_id IN ('sales', 'config')
						) THEN 1 ELSE 0
					END
				) AS can_manage_links,
				COALESCE(cp.can_view_orders,
					CASE
						WHEN e.person_id = 1 THEN 1
						WHEN EXISTS (
							SELECT 1 FROM phppos_permissions pp
							WHERE pp.person_id = e.person_id
							AND pp.module_id IN ('sales', 'reports', 'config')
						) THEN 1 ELSE 0
					END
				) AS can_view_orders,
				COALESCE(cp.can_manage_inventory,
					CASE
						WHEN e.person_id = 1 THEN 1
						WHEN EXISTS (
							SELECT 1 FROM phppos_permissions pp
							WHERE pp.person_id = e.person_id
							AND pp.module_id IN ('items', 'config')
						) THEN 1 ELSE 0
					END
				) AS can_manage_inventory,
				COALESCE(cp.can_manage_catalog,
					CASE
						WHEN e.person_id = 1 THEN 1
						WHEN EXISTS (
							SELECT 1 FROM phppos_permissions pp
							WHERE pp.person_id = e.person_id
							AND pp.module_id IN ('config')
						) THEN 1 ELSE 0
					END
				) AS can_manage_catalog,
				COALESCE(cp.can_download_reports,
					CASE
						WHEN e.person_id = 1 THEN 1
						WHEN EXISTS (
							SELECT 1 FROM phppos_permissions pp
							WHERE pp.person_id = e.person_id
							AND pp.module_id IN ('sales', 'reports', 'config')
						) THEN 1 ELSE 0
					END
				) AS can_download_reports,
				COALESCE(cp.can_manage_permissions,
					CASE
						WHEN e.person_id = 1 THEN 1
						WHEN EXISTS (
							SELECT 1 FROM phppos_permissions pp
							WHERE pp.person_id = e.person_id
							AND pp.module_id IN ('config')
						) THEN 1 ELSE 0
					END
				) AS can_manage_permissions
			FROM phppos_employees e
			LEFT JOIN phppos_people p ON p.person_id = e.person_id
			LEFT JOIN catalogo.catalog_employee_permissions cp ON cp.person_id = e.person_id
			WHERE e.deleted = 0
			ORDER BY p.first_name ASC, p.last_name ASC
		";

		return $this->db_gala->query($sql)->result_array();
	}

	public function upsert_permission($person_id, $payload, $updated_by)
	{
		$exists = $this->db_catalog
			->from('catalog_employee_permissions')
			->where('person_id', (int) $person_id)
			->limit(1)
			->get()
			->row_array();

		$can_view_dashboard = !empty($payload['can_view_dashboard']) ? 1 : 0;
		$can_manage_links = !empty($payload['can_manage_links']) || !empty($payload['can_create_links']) ? 1 : 0;
		$can_view_orders = !empty($payload['can_view_orders']) ? 1 : 0;
		$can_manage_inventory = !empty($payload['can_manage_inventory']) ? 1 : 0;
		$can_manage_catalog = !empty($payload['can_manage_catalog']) ? 1 : 0;
		$can_download_reports = !empty($payload['can_download_reports']) ? 1 : 0;
		$can_manage_permissions = !empty($payload['can_manage_permissions']) ? 1 : 0;
		$can_access_panel = ($can_view_dashboard || $can_manage_links || $can_view_orders || $can_manage_inventory || $can_manage_catalog || $can_manage_permissions) ? 1 : 0;

		$data = array(
			'person_id' => (int) $person_id,
			'can_access_panel' => $can_access_panel,
			'can_view_dashboard' => $can_view_dashboard,
			'can_manage_links' => $can_manage_links,
			'can_create_links' => $can_manage_links,
			'can_view_orders' => $can_view_orders,
			'can_manage_inventory' => $can_manage_inventory,
			'can_manage_catalog' => $can_manage_catalog,
			'can_download_reports' => $can_download_reports,
			'can_manage_permissions' => $can_manage_permissions,
			'updated_at' => date('Y-m-d H:i:s'),
			'updated_by' => (int) $updated_by
		);

		if ($exists)
		{
			$this->db_catalog->where('person_id', (int) $person_id)->update('catalog_employee_permissions', $data);
			return;
		}

		$this->db_catalog->insert('catalog_employee_permissions', $data);
	}

	public function get_locations()
	{
		return $this->db_gala
			->select('location_id, name')
			->from('phppos_locations')
			->where('deleted', 0)
			->order_by('location_id', 'ASC')
			->get()
			->result_array();
	}

	public function get_location_ids_for_employee($employee_person_id)
	{
		$rows = $this->db_gala
			->select('el.location_id')
			->from('phppos_employees_locations el')
			->join('phppos_locations l', 'l.location_id = el.location_id', 'inner')
			->where('el.employee_id', (int) $employee_person_id)
			->where('l.deleted', 0)
			->order_by('el.location_id', 'ASC')
			->get()
			->result_array();

		$ids = array();
		foreach ($rows as $row)
		{
			$ids[] = (int) $row['location_id'];
		}

		return array_values(array_unique($ids));
	}

	public function get_employee_locations_map()
	{
		$rows = $this->db_gala
			->select('el.employee_id, el.location_id')
			->from('phppos_employees_locations el')
			->join('phppos_locations l', 'l.location_id = el.location_id', 'inner')
			->where('l.deleted', 0)
			->order_by('el.employee_id', 'ASC')
			->order_by('el.location_id', 'ASC')
			->get()
			->result_array();

		$map = array();
		foreach ($rows as $row)
		{
			$employee_id = (int) $row['employee_id'];
			$location_id = (int) $row['location_id'];
			if (!isset($map[$employee_id]))
			{
				$map[$employee_id] = array();
			}
			$map[$employee_id][] = $location_id;
		}

		foreach ($map as $employee_id => $ids)
		{
			$map[$employee_id] = array_values(array_unique($ids));
		}

		return $map;
	}

	public function get_customer($customer_id)
	{
		return $this->db_gala
			->select('c.id, c.tier_id, c.company_name, p.first_name, p.last_name, p.email')
			->from('phppos_customers c')
			->join('phppos_people p', 'p.person_id = c.person_id', 'left')
			->where('c.id', (int) $customer_id)
			->where('c.deleted', 0)
			->limit(1)
			->get()
			->row_array();
	}

	public function get_inventory_items($limit = 250)
	{
		$sql = "
			SELECT
				i.item_id,
				i.name,
				COALESCE(i.item_number, i.product_id, '') AS model_code,
				COALESCE(i.is_ecommerce, 0) AS is_ecommerce,
				COALESCE(i.main_image_id, img.first_image_id) AS main_image_id,
				COALESCE(img.image_count, 0) AS image_count,
				COALESCE(img.image_ids, '') AS image_ids
			FROM phppos_items i
			LEFT JOIN (
				SELECT
					ii.item_id,
					CAST(SUBSTRING_INDEX(
						GROUP_CONCAT(
							ii.image_id
							ORDER BY
								CASE
									WHEN i2.main_image_id IS NOT NULL AND ii.image_id = i2.main_image_id THEN 0
									ELSE 1
								END,
								COALESCE(cis.sort_order, 2147483647),
								ii.id ASC
							SEPARATOR ','
						),
						',',
						1
					) AS UNSIGNED) AS first_image_id,
					COUNT(*) AS image_count,
					GROUP_CONCAT(
						ii.image_id
						ORDER BY
							CASE
								WHEN i2.main_image_id IS NOT NULL AND ii.image_id = i2.main_image_id THEN 0
								ELSE 1
							END,
							COALESCE(cis.sort_order, 2147483647),
							ii.id ASC
						SEPARATOR ','
					) AS image_ids
				FROM phppos_item_images ii
				INNER JOIN phppos_items i2 ON i2.item_id = ii.item_id
				LEFT JOIN catalogo.catalog_item_image_sort cis
					ON cis.item_id = ii.item_id
					AND cis.image_id = ii.image_id
				GROUP BY ii.item_id
			) img ON img.item_id = i.item_id
			WHERE i.deleted = 0
			ORDER BY i.item_id DESC
			LIMIT ?
		";

		$rows = $this->db_gala->query($sql, array((int) $limit))->result_array();
		$rows = $this->normalize_inventory_rows($rows);
		return $rows;
	}

	public function get_inventory_item($item_id)
	{
		$sql = "
			SELECT
				i.item_id,
				i.name,
				COALESCE(i.item_number, i.product_id, '') AS model_code,
				COALESCE(i.is_ecommerce, 0) AS is_ecommerce,
				COALESCE(i.main_image_id, img.first_image_id) AS main_image_id,
				COALESCE(img.image_count, 0) AS image_count,
				COALESCE(img.image_ids, '') AS image_ids
			FROM phppos_items i
			LEFT JOIN (
				SELECT
					ii.item_id,
					CAST(SUBSTRING_INDEX(
						GROUP_CONCAT(
							ii.image_id
							ORDER BY
								CASE
									WHEN i2.main_image_id IS NOT NULL AND ii.image_id = i2.main_image_id THEN 0
									ELSE 1
								END,
								COALESCE(cis.sort_order, 2147483647),
								ii.id ASC
							SEPARATOR ','
						),
						',',
						1
					) AS UNSIGNED) AS first_image_id,
					COUNT(*) AS image_count,
					GROUP_CONCAT(
						ii.image_id
						ORDER BY
							CASE
								WHEN i2.main_image_id IS NOT NULL AND ii.image_id = i2.main_image_id THEN 0
								ELSE 1
							END,
							COALESCE(cis.sort_order, 2147483647),
							ii.id ASC
						SEPARATOR ','
					) AS image_ids
				FROM phppos_item_images ii
				INNER JOIN phppos_items i2 ON i2.item_id = ii.item_id
				LEFT JOIN catalogo.catalog_item_image_sort cis
					ON cis.item_id = ii.item_id
					AND cis.image_id = ii.image_id
				GROUP BY ii.item_id
			) img ON img.item_id = i.item_id
			WHERE i.deleted = 0
				AND i.item_id = ?
			LIMIT 1
		";

		$rows = $this->db_gala->query($sql, array((int) $item_id))->result_array();
		$rows = $this->normalize_inventory_rows($rows);
		return !empty($rows) ? $rows[0] : NULL;
	}

	private function normalize_inventory_rows($rows)
	{
		foreach ($rows as &$row)
		{
			$row['item_id'] = (int) $row['item_id'];
			$row['is_ecommerce'] = (int) $row['is_ecommerce'];
			$row['main_image_id'] = !empty($row['main_image_id']) ? (int) $row['main_image_id'] : NULL;
			$row['image_count'] = (int) $row['image_count'];
			$raw = isset($row['image_ids']) ? (string) $row['image_ids'] : '';
			$row['image_ids'] = array();
			if ($raw !== '')
			{
				$parts = explode(',', $raw);
				foreach ($parts as $id)
				{
					$id = (int) $id;
					if ($id > 0)
					{
						$row['image_ids'][] = $id;
					}
				}
				$row['image_ids'] = array_values(array_unique($row['image_ids']));
			}

			if (!empty($row['main_image_id']) && in_array((int) $row['main_image_id'], $row['image_ids'], TRUE))
			{
				$main = (int) $row['main_image_id'];
				$row['image_ids'] = array_values(array_diff($row['image_ids'], array($main)));
				array_unshift($row['image_ids'], $main);
			}
		}

		return $rows;
	}

	public function update_inventory_item_ecommerce($item_id, $is_ecommerce)
	{
		$this->db_gala
			->where('item_id', (int) $item_id)
			->where('deleted', 0)
			->update('phppos_items', array(
				'is_ecommerce' => (int) $is_ecommerce,
				'last_edited' => date('Y-m-d H:i:s')
			));

		return $this->db_gala->affected_rows() >= 0;
	}

	public function add_inventory_item_image($item_id, $file_name, $file_data)
	{
		$item_id = (int) $item_id;
		if ($item_id <= 0)
		{
			return array('ok' => FALSE, 'message' => 'Item invalido.');
		}

		$this->db_gala->trans_begin();

		$this->db_gala->insert('phppos_app_files', array(
			'file_name' => (string) $file_name,
			'file_data' => $file_data
		));
		$file_id = (int) $this->db_gala->insert_id();

		$this->db_gala->insert('phppos_item_images', array(
			'title' => '',
			'alt_text' => '',
			'item_id' => $item_id,
			'item_variation_id' => NULL,
			'ecommerce_image_id' => NULL,
			'image_id' => $file_id
		));

		$item = $this->db_gala
			->select('main_image_id')
			->from('phppos_items')
			->where('item_id', $item_id)
			->limit(1)
			->get()
			->row_array();
		if ($item && empty($item['main_image_id']))
		{
			$this->db_gala
				->where('item_id', $item_id)
				->update('phppos_items', array('main_image_id' => $file_id));
		}

		if ($this->db_gala->trans_status() === FALSE)
		{
			$this->db_gala->trans_rollback();
			return array('ok' => FALSE, 'message' => 'No se pudo guardar la imagen.');
		}

		$this->db_gala->trans_commit();
		return array('ok' => TRUE, 'file_id' => $file_id);
	}

	public function delete_inventory_item_image($item_id, $image_id)
	{
		$item_id = (int) $item_id;
		$image_id = (int) $image_id;
		if ($item_id <= 0 || $image_id <= 0)
		{
			return array('ok' => FALSE, 'message' => 'Datos invalidos.');
		}

		$exists = $this->db_gala
			->from('phppos_item_images')
			->where('item_id', $item_id)
			->where('image_id', $image_id)
			->limit(1)
			->get()
			->row_array();
		if (!$exists)
		{
			return array('ok' => FALSE, 'message' => 'La imagen no existe para este item.');
		}

		$this->db_gala->trans_begin();

		$item = $this->db_gala
			->select('main_image_id')
			->from('phppos_items')
			->where('item_id', $item_id)
			->limit(1)
			->get()
			->row_array();

		$remaining = $this->db_gala
			->select('ii.image_id')
			->from('phppos_item_images ii')
			->join('catalogo.catalog_item_image_sort cis', 'cis.item_id = ii.item_id AND cis.image_id = ii.image_id', 'left')
			->where('ii.item_id', $item_id)
			->where('ii.image_id !=', $image_id)
			->order_by('COALESCE(cis.sort_order, 2147483647)', 'ASC', FALSE)
			->order_by('ii.id', 'ASC')
			->limit(1)
			->get()
			->row_array();

		if ($item && !empty($item['main_image_id']) && (int) $item['main_image_id'] === $image_id)
		{
			$this->db_gala
				->where('item_id', $item_id)
				->update('phppos_items', array('main_image_id' => !empty($remaining['image_id']) ? (int) $remaining['image_id'] : NULL));
		}

		$this->db_gala
			->where('item_id', $item_id)
			->where('image_id', $image_id)
			->delete('phppos_item_images');

		if ($this->db_gala->trans_status() === FALSE)
		{
			$this->db_gala->trans_rollback();
			return array('ok' => FALSE, 'message' => 'No se pudo eliminar la imagen.');
		}
		$this->db_gala->trans_commit();

		$this->db_catalog
			->where('item_id', $item_id)
			->where('image_id', $image_id)
			->delete('catalog_item_image_sort');

		$used_elsewhere = $this->db_gala
			->from('phppos_item_images')
			->where('image_id', $image_id)
			->limit(1)
			->get()
			->row_array();
		if (!$used_elsewhere)
		{
			$this->db_gala->where('file_id', $image_id)->delete('phppos_app_files');
		}

		return array('ok' => TRUE);
	}

	public function save_inventory_item_image_order($item_id, $ordered_image_ids)
	{
		$item_id = (int) $item_id;
		if ($item_id <= 0)
		{
			return array('ok' => FALSE, 'message' => 'Item invalido.');
		}

		$existing_rows = $this->db_gala
			->select('image_id')
			->from('phppos_item_images')
			->where('item_id', $item_id)
			->get()
			->result_array();
		$existing = array();
		foreach ($existing_rows as $row)
		{
			$existing[] = (int) $row['image_id'];
		}
		$existing = array_values(array_unique($existing));

		$clean = array();
		if (is_array($ordered_image_ids))
		{
			foreach ($ordered_image_ids as $img_id)
			{
				$img_id = (int) $img_id;
				if ($img_id > 0 && in_array($img_id, $existing, TRUE) && !in_array($img_id, $clean, TRUE))
				{
					$clean[] = $img_id;
				}
			}
		}
		foreach ($existing as $img_id)
		{
			if (!in_array($img_id, $clean, TRUE))
			{
				$clean[] = $img_id;
			}
		}

		$this->db_catalog->trans_begin();
		$this->db_catalog->where('item_id', $item_id)->delete('catalog_item_image_sort');
		$rank = 1;
		foreach ($clean as $img_id)
		{
			$this->db_catalog->insert('catalog_item_image_sort', array(
				'item_id' => $item_id,
				'image_id' => (int) $img_id,
				'sort_order' => $rank++,
				'updated_at' => date('Y-m-d H:i:s')
			));
		}

		$new_main_image_id = !empty($clean) ? (int) $clean[0] : NULL;
		$this->db_gala
			->where('item_id', $item_id)
			->update('phppos_items', array(
				'main_image_id' => $new_main_image_id,
				'last_edited' => date('Y-m-d H:i:s')
			));

		if ($this->db_catalog->trans_status() === FALSE || $this->db_gala->trans_status() === FALSE)
		{
			$this->db_catalog->trans_rollback();
			return array('ok' => FALSE, 'message' => 'No se pudo guardar el orden de fotos.');
		}
		$this->db_catalog->trans_commit();
		return array('ok' => TRUE);
	}

	public function create_link($payload)
	{
		$this->db_catalog->insert('catalog_links', $payload);
		return (int) $this->db_catalog->insert_id();
	}

	public function get_link_by_id($id)
	{
		return $this->db_catalog
			->from('catalog_links')
			->where('id', (int) $id)
			->limit(1)
			->get()
			->row_array();
	}

	public function get_link_by_token($token)
	{
		$link = $this->db_catalog
			->from('catalog_links')
			->where('token', $token)
			->where('status', 1)
			->limit(1)
			->get()
			->row_array();

		if ($link)
		{
			$this->db_catalog
				->where('id', (int) $link['id'])
				->update('catalog_links', array('last_opened_at' => date('Y-m-d H:i:s')));
		}

		return $link;
	}

	public function get_link_by_token_any($token)
	{
		return $this->db_catalog
			->from('catalog_links')
			->where('token', $token)
			->limit(1)
			->get()
			->row_array();
	}

	public function get_links($limit = 30)
	{
		return $this->db_catalog
			->from('catalog_links')
			->where('status', 1)
			->where('expires_at >=', date('Y-m-d H:i:s'))
			->order_by('id', 'DESC')
			->limit((int) $limit)
			->get()
			->result_array();
	}

	public function get_links_history($limit = 200)
	{
		return $this->db_catalog
			->from('catalog_links')
			->group_start()
				->where('status !=', 1)
				->or_where('expires_at <', date('Y-m-d H:i:s'))
			->group_end()
			->order_by('id', 'DESC')
			->limit((int) $limit)
			->get()
			->result_array();
	}

	public function get_latest_active_public_link()
	{
		return $this->db_catalog
			->from('catalog_links')
			->where('status', 1)
			->where('customer_id', 0)
			->where('expires_at >=', date('Y-m-d H:i:s'))
			->order_by('id', 'DESC')
			->limit(1)
			->get()
			->row_array();
	}

	public function deactivate_link($link_id)
	{
		return $this->db_catalog
			->where('id', (int) $link_id)
			->update('catalog_links', array(
				'status' => 0
			));
	}

	public function make_link_public($link_id)
	{
		return $this->db_catalog
			->where('id', (int) $link_id)
			->update('catalog_links', array(
				'customer_id' => 0,
				'customer_name' => 'Catalogo publico',
				'tier_id' => NULL
			));
	}

	public function get_products_for_link($link, $location_id)
	{
		$selected_tier_id = (int) $this->get_setting('catalog_price_tier_id', '0');
		if ($selected_tier_id > 0)
		{
			$tier_id = $selected_tier_id;
		}
		else
		{
			$tier_id = isset($link['tier_id']) ? (int) $link['tier_id'] : 0;
		}
		$location_id = (int) $location_id;
		$today = date('Y-m-d');
		$product_order = $this->get_setting('product_order', 'name_asc');
		$min_visible_stock = (int) $this->get_setting('min_visible_stock', '0');
		if ($min_visible_stock < 0)
		{
			$min_visible_stock = 0;
		}

		$order_sql = 'i.name ASC';
		if ($product_order === 'name_desc')
		{
			$order_sql = 'i.name DESC';
		}
		elseif ($product_order === 'newest')
		{
			$order_sql = 'i.item_id DESC';
		}
		elseif ($product_order === 'oldest')
		{
			$order_sql = 'i.item_id ASC';
		}

		$sql = "
			SELECT
				i.item_id,
				i.name,
				i.category_id,
				COALESCE(cat.name, 'Sin categoria') AS category_name,
				i.description,
				i.long_description,
				i.cost_price,
				i.unit_price AS default_unit_price,
				COALESCE(img.main_image_id, img.first_image_id) AS image_id,
				COALESCE(img.image_count, 0) AS image_count,
				COALESCE(img.image_ids, '') AS image_ids,
				COALESCE(li.unit_price, i.unit_price) AS base_price,
				CASE
					WHEN li.promo_price IS NOT NULL
						AND li.start_date IS NOT NULL
						AND li.end_date IS NOT NULL
						AND ? BETWEEN li.start_date AND li.end_date
					THEN li.promo_price
					WHEN i.promo_price IS NOT NULL
						AND i.start_date IS NOT NULL
						AND i.end_date IS NOT NULL
						AND ? BETWEEN i.start_date AND i.end_date
					THEN i.promo_price
					ELSE NULL
				END AS promo_price,
				CASE
					WHEN litp.unit_price IS NOT NULL AND litp.unit_price > 0 THEN litp.unit_price
					WHEN litp.percent_off IS NOT NULL THEN ROUND(COALESCE(li.unit_price, i.unit_price) * (1 - (litp.percent_off / 100)), 10)
					WHEN litp.cost_plus_percent IS NOT NULL THEN ROUND(i.cost_price * (1 + (litp.cost_plus_percent / 100)), 10)
					WHEN litp.cost_plus_fixed_amount IS NOT NULL THEN ROUND(i.cost_price + litp.cost_plus_fixed_amount, 10)
					ELSE NULL
				END AS location_tier_price,
				CASE
					WHEN itp.unit_price IS NOT NULL AND itp.unit_price > 0 THEN itp.unit_price
					WHEN itp.percent_off IS NOT NULL THEN ROUND(COALESCE(li.unit_price, i.unit_price) * (1 - (itp.percent_off / 100)), 10)
					WHEN itp.cost_plus_percent IS NOT NULL THEN ROUND(i.cost_price * (1 + (itp.cost_plus_percent / 100)), 10)
					WHEN itp.cost_plus_fixed_amount IS NOT NULL THEN ROUND(i.cost_price + itp.cost_plus_fixed_amount, 10)
					ELSE NULL
				END AS global_tier_price
			FROM phppos_items i
			LEFT JOIN phppos_categories cat
				ON cat.id = i.category_id
				AND COALESCE(cat.deleted, 0) = 0
			LEFT JOIN catalogo.catalog_category_sort csort
				ON csort.category_id = i.category_id
			LEFT JOIN phppos_location_items li
				ON li.item_id = i.item_id
				AND li.location_id = ?
			LEFT JOIN phppos_location_items_tier_prices litp
				ON litp.item_id = i.item_id
				AND litp.location_id = ?
				AND litp.tier_id = ?
			LEFT JOIN phppos_items_tier_prices itp
				ON itp.item_id = i.item_id
				AND itp.tier_id = ?
			LEFT JOIN (
				SELECT
					ii.item_id,
					MAX(CASE WHEN i2.main_image_id IS NOT NULL AND ii.image_id = i2.main_image_id THEN ii.image_id ELSE NULL END) AS main_image_id,
					CAST(SUBSTRING_INDEX(
						GROUP_CONCAT(
							ii.image_id
							ORDER BY
								COALESCE(cis.sort_order,
									CASE
										WHEN i2.main_image_id IS NOT NULL AND ii.image_id = i2.main_image_id THEN 0
										ELSE 2147483647
									END
								),
								ii.id ASC
							SEPARATOR ','
						),
						',',
						1
					) AS UNSIGNED) AS first_image_id,
					COUNT(*) AS image_count,
					GROUP_CONCAT(
						ii.image_id
						ORDER BY
							COALESCE(cis.sort_order,
								CASE
									WHEN i2.main_image_id IS NOT NULL AND ii.image_id = i2.main_image_id THEN 0
									ELSE 2147483647
								END
							),
							ii.id ASC
						SEPARATOR ','
					) AS image_ids
				FROM phppos_item_images ii
				INNER JOIN phppos_items i2 ON i2.item_id = ii.item_id
				LEFT JOIN catalogo.catalog_item_image_sort cis
					ON cis.item_id = ii.item_id
					AND cis.image_id = ii.image_id
				GROUP BY ii.item_id
			) img ON img.item_id = i.item_id
			WHERE i.deleted = 0
				AND COALESCE(i.item_inactive, 0) = 0
				AND COALESCE(i.is_ecommerce, 1) = 1
				AND COALESCE(img.main_image_id, img.first_image_id) IS NOT NULL
				AND COALESCE(li.quantity, 0) >= ?
			ORDER BY COALESCE(csort.sort_order, 9999) ASC, category_name ASC, " . $order_sql . "
		";

		$rows = $this->db_gala->query($sql, array(
			$today,
			$today,
			$location_id,
			$location_id,
			$tier_id,
			$tier_id,
			$min_visible_stock
		))->result_array();

		foreach ($rows as &$row)
		{
			$final_price = $row['base_price'];
			if ($row['promo_price'] !== NULL)
			{
				$final_price = $row['promo_price'];
			}

			if ($row['global_tier_price'] !== NULL)
			{
				$final_price = $row['global_tier_price'];
			}

			if ($row['location_tier_price'] !== NULL)
			{
				$final_price = $row['location_tier_price'];
			}

			$row['final_price'] = (float) $final_price;
			$row['cost_price'] = (float) $row['cost_price'];
			$row['base_price'] = (float) $row['base_price'];
			$row['category_id'] = !empty($row['category_id']) ? (int) $row['category_id'] : 0;
			$row['image_id'] = $row['image_id'] ? (int) $row['image_id'] : NULL;
			$row['image_count'] = (int) $row['image_count'];
			$raw_image_ids = isset($row['image_ids']) ? $row['image_ids'] : '';
			$row['image_ids'] = array();
			if (!empty($raw_image_ids))
			{
				$ids = explode(',', $raw_image_ids);
				$clean = array();
				foreach ($ids as $id)
				{
					$id = (int) $id;
					if ($id > 0)
					{
						$clean[] = $id;
					}
				}
				$row['image_ids'] = array_values(array_unique($clean));
			}
			elseif (!empty($row['image_id']))
			{
				$row['image_ids'] = array((int) $row['image_id']);
			}
		}

		return $rows;
	}

	public function create_order($link, $cart_rows, $customer_note, $settings)
	{
		$this->db_catalog->trans_begin();

		$subtotal = 0;
		foreach ($cart_rows as $row)
		{
			$subtotal += $row['line_total'];
		}

		$order_data = array(
			'link_id' => (int) $link['id'],
			'customer_id' => (int) $link['customer_id'],
			'customer_note' => $customer_note,
			'ordered_at' => date('Y-m-d H:i:s'),
			'subtotal' => $subtotal,
			'tax' => 0,
			'total' => $subtotal,
			'status' => 'submitted'
		);

		$this->db_catalog->insert('catalog_orders', $order_data);
		$order_id = (int) $this->db_catalog->insert_id();

		foreach ($cart_rows as $row)
		{
			$this->db_catalog->insert('catalog_order_items', array(
				'order_id' => $order_id,
				'item_id' => (int) $row['item_id'],
				'item_name' => $row['item_name'],
				'quantity' => $row['quantity'],
				'unit_price' => $row['unit_price'],
				'line_total' => $row['line_total'],
				'image_id' => $row['image_id']
			));
		}

		$sale_id = NULL;
		$this->db_catalog
			->where('id', $order_id)
			->update('catalog_orders', array('sale_id' => NULL));

		if ($this->db_catalog->trans_status() === FALSE)
		{
			$this->db_catalog->trans_rollback();
			return array('ok' => FALSE, 'message' => 'No se pudo guardar el pedido.');
		}

		$this->db_catalog->trans_commit();

		return array(
			'ok' => TRUE,
			'order_id' => $order_id,
			'sale_id' => $sale_id
		);
	}

	public function get_recent_orders($limit = 40)
	{
		return $this->db_catalog
			->select("o.*, l.token, COALESCE(NULLIF(c.company_name, ''), NULLIF(TRIM(CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, ''))), ''), l.customer_name) AS customer_name, l.employee_id, l.location_id, ps.suspended AS sale_suspended, ps.deleted AS sale_deleted", FALSE)
			->from('catalog_orders o')
			->join('catalog_links l', 'l.id = o.link_id', 'left')
			->join('gala.phppos_customers c', 'c.id = o.customer_id AND c.deleted = 0', 'left')
			->join('gala.phppos_people p', 'p.person_id = c.person_id', 'left')
			->join('gala.phppos_sales ps', 'ps.sale_id = o.sale_id', 'left')
			->where('o.status !=', 'deleted')
			->group_start()
				->where('COALESCE(o.sale_id, 0) = 0', NULL, FALSE)
				->or_where('ps.sale_id IS NULL', NULL, FALSE)
				->or_group_start()
					->where('COALESCE(ps.suspended, 0) = 1', NULL, FALSE)
					->where('COALESCE(ps.deleted, 0) = 0', NULL, FALSE)
				->group_end()
			->group_end()
			->order_by('o.id', 'DESC')
			->limit((int) $limit)
			->get()
			->result_array();
	}

	public function get_order_history($limit = 200)
	{
		$this->db_catalog
			->select("o.*, l.token, COALESCE(NULLIF(c.company_name, ''), NULLIF(TRIM(CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, ''))), ''), l.customer_name) AS customer_name, l.employee_id, l.location_id, ps.suspended AS sale_suspended, ps.deleted AS sale_deleted", FALSE)
			->from('catalog_orders o')
			->join('catalog_links l', 'l.id = o.link_id', 'left')
			->join('gala.phppos_customers c', 'c.id = o.customer_id AND c.deleted = 0', 'left')
			->join('gala.phppos_people p', 'p.person_id = c.person_id', 'left')
			->join('gala.phppos_sales ps', 'ps.sale_id = o.sale_id', 'left')
			->group_start()
				->where('o.status', 'deleted')
				->or_group_start()
					->where('COALESCE(o.sale_id, 0) > 0', NULL, FALSE)
					->group_start()
						->where('ps.sale_id IS NULL', NULL, FALSE)
						->or_where('COALESCE(ps.suspended, 0) = 0', NULL, FALSE)
						->or_where('COALESCE(ps.deleted, 0) = 1', NULL, FALSE)
					->group_end()
				->group_end()
			->group_end()
			->order_by('o.id', 'DESC')
			->limit((int) $limit);

		return $this->db_catalog->get()->result_array();
	}

	public function get_order($order_id)
	{
		return $this->db_catalog
			->select("o.*, l.token, COALESCE(NULLIF(c.company_name, ''), NULLIF(TRIM(CONCAT(COALESCE(p.first_name, ''), ' ', COALESCE(p.last_name, ''))), ''), l.customer_name) AS customer_name, l.employee_id, l.location_id", FALSE)
			->from('catalog_orders o')
			->join('catalog_links l', 'l.id = o.link_id', 'left')
			->join('gala.phppos_customers c', 'c.id = o.customer_id AND c.deleted = 0', 'left')
			->join('gala.phppos_people p', 'p.person_id = c.person_id', 'left')
			->where('o.id', (int) $order_id)
			->limit(1)
			->get()
			->row_array();
	}

	public function get_order_items($order_id)
	{
		return $this->db_catalog
			->from('catalog_order_items')
			->where('order_id', (int) $order_id)
			->order_by('id', 'ASC')
			->get()
			->result_array();
	}

	public function assign_order_customer($order_id, $customer_id, $default_employee_id = NULL, $default_location_id = NULL)
	{
		$order_id = (int) $order_id;
		$customer_id = (int) $customer_id;
		$default_employee_id = (int) $default_employee_id;
		$default_location_id = (int) $default_location_id;
		if ($order_id <= 0 || $customer_id <= 0)
		{
			return array('ok' => FALSE, 'message' => 'Pedido o cliente invalido.');
		}

		$order = $this->db_catalog
			->select('id, link_id, status')
			->from('catalog_orders')
			->where('id', $order_id)
			->limit(1)
			->get()
			->row_array();
		if (!$order)
		{
			return array('ok' => FALSE, 'message' => 'Pedido no encontrado.');
		}

		if (!isset($order['status']) || (string) $order['status'] !== 'cliente_nuevo')
		{
			return array('ok' => FALSE, 'message' => 'Solo se puede asignar cliente en pedidos de catalogo independiente.');
		}

		$customer = $this->get_customer($customer_id);
		if (!$customer)
		{
			return array('ok' => FALSE, 'message' => 'Cliente PHPPOS invalido.');
		}

		$customer_name = trim((string) $customer['company_name']) !== ''
			? (string) $customer['company_name']
			: trim((string) $customer['first_name'] . ' ' . (string) $customer['last_name']);
		if ($customer_name === '')
		{
			$customer_name = 'Cliente #' . (int) $customer_id;
		}

		$this->db_catalog->trans_begin();
		$assigned_employee_id = 0;
		$assigned_location_id = 0;
		$this->db_catalog
			->where('id', $order_id)
			->update('catalog_orders', array(
				'customer_id' => $customer_id
			));

		if (!empty($order['link_id']) && (int) $order['link_id'] > 0)
		{
			$link_row = $this->db_catalog
				->select('employee_id, location_id')
				->from('catalog_links')
				->where('id', (int) $order['link_id'])
				->limit(1)
				->get()
				->row_array();

			$link_employee_id = !empty($link_row['employee_id']) ? (int) $link_row['employee_id'] : 0;
			$link_location_id = !empty($link_row['location_id']) ? (int) $link_row['location_id'] : 0;

			$resolve_employee_id = $link_employee_id;
			if ($resolve_employee_id <= 0 && $default_employee_id > 0)
			{
				$resolve_employee_id = $default_employee_id;
			}
			if ($resolve_employee_id <= 0)
			{
				$fallback_emp = $this->db_gala
					->select('person_id')
					->from('phppos_employees')
					->where('deleted', 0)
					->order_by('person_id', 'ASC')
					->limit(1)
					->get()
					->row_array();
				if (!empty($fallback_emp['person_id']))
				{
					$resolve_employee_id = (int) $fallback_emp['person_id'];
				}
			}

			$allowed_locations = array();
			if ($resolve_employee_id > 0)
			{
				$allowed_locations = $this->get_location_ids_for_employee($resolve_employee_id);
			}

			$resolve_location_id = $link_location_id;
			if ($resolve_location_id <= 0 && $default_location_id > 0)
			{
				$resolve_location_id = $default_location_id;
			}
			if (!empty($allowed_locations))
			{
				if (!in_array($resolve_location_id, $allowed_locations, TRUE))
				{
					$resolve_location_id = (int) $allowed_locations[0];
				}
			}
			elseif ($resolve_location_id <= 0)
			{
				$fallback_loc = $this->db_gala
					->select('location_id')
					->from('phppos_locations')
					->where('deleted', 0)
					->order_by('location_id', 'ASC')
					->limit(1)
					->get()
					->row_array();
				if (!empty($fallback_loc['location_id']))
				{
					$resolve_location_id = (int) $fallback_loc['location_id'];
				}
			}

			$this->db_catalog
				->where('id', (int) $order['link_id'])
				->update('catalog_links', array(
					'customer_id' => $customer_id,
					'customer_name' => $customer_name,
					'tier_id' => !empty($customer['tier_id']) ? (int) $customer['tier_id'] : NULL,
					'employee_id' => $resolve_employee_id > 0 ? $resolve_employee_id : NULL,
					'location_id' => $resolve_location_id > 0 ? $resolve_location_id : NULL
				));
			$assigned_employee_id = $resolve_employee_id > 0 ? $resolve_employee_id : 0;
			$assigned_location_id = $resolve_location_id > 0 ? $resolve_location_id : 0;
		}

		if ($this->db_catalog->trans_status() === FALSE)
		{
			$this->db_catalog->trans_rollback();
			return array('ok' => FALSE, 'message' => 'No se pudo asignar el cliente.');
		}

		$this->db_catalog->trans_commit();
		return array(
			'ok' => TRUE,
			'customer_id' => $customer_id,
			'customer_name' => $customer_name,
			'employee_id' => $assigned_employee_id,
			'location_id' => $assigned_location_id
		);
	}

	public function process_order_to_suspended_sale($order_id)
	{
		$order = $this->get_order((int) $order_id);
		if (!$order)
		{
			return array('ok' => FALSE, 'message' => 'Pedido no encontrado.');
		}

		if (!empty($order['sale_id']) && (int) $order['sale_id'] > 0)
		{
			return array('ok' => TRUE, 'sale_id' => (int) $order['sale_id'], 'already_processed' => TRUE);
		}

		$items = $this->get_order_items((int) $order_id);
		if (empty($items))
		{
			return array('ok' => FALSE, 'message' => 'El pedido no tiene items para procesar.');
		}

		$employee_id = (int) $order['employee_id'];
		$location_id = (int) $order['location_id'];
		if ($employee_id <= 0 || $location_id <= 0)
		{
			return array('ok' => FALSE, 'message' => 'El pedido no tiene empleado/sucursal validos.');
		}

		// phppos_sales.customer_id referencia phppos_customers.person_id (no phppos_customers.id)
		$sales_customer_person_id = NULL;
		$catalog_customer_id = (int) $order['customer_id'];
		if ($catalog_customer_id > 0)
		{
			$customer_row = $this->db_gala
				->select('person_id')
				->from('phppos_customers')
				->where('id', $catalog_customer_id)
				->where('deleted', 0)
				->limit(1)
				->get()
				->row_array();

			if (!empty($customer_row['person_id']))
			{
				$sales_customer_person_id = (int) $customer_row['person_id'];
			}
		}

		$item_ids = array();
		foreach ($items as $item)
		{
			$item_ids[] = (int) $item['item_id'];
		}
		$item_ids = array_values(array_unique($item_ids));

		$item_cost_map = array();
		if (!empty($item_ids))
		{
			$rows = $this->db_gala
				->select('item_id, cost_price')
				->from('phppos_items')
				->where_in('item_id', $item_ids)
				->get()
				->result_array();
			foreach ($rows as $row)
			{
				$item_cost_map[(int) $row['item_id']] = (float) $row['cost_price'];
			}
		}

		$total_quantity = 0;
		$subtotal = 0;
		$tax = 0;
		$total = 0;
		$profit = 0;
		foreach ($items as $item)
		{
			$qty = (float) $item['quantity'];
			$line_subtotal = (float) $item['line_total'];
			$cost_price = isset($item_cost_map[(int) $item['item_id']]) ? (float) $item_cost_map[(int) $item['item_id']] : 0;
			$line_profit = $line_subtotal - ($cost_price * $qty);

			$total_quantity += $qty;
			$subtotal += $line_subtotal;
			$total += $line_subtotal;
			$profit += $line_profit;
		}

		$exchange_symbol = (string) $this->get_app_config_value('currency_symbol', '$');
		$exchange_symbol_location = (string) $this->get_app_config_value('currency_symbol_location', 'before');
		$exchange_decimals = (string) $this->get_app_config_value('number_of_decimals', '');
		$exchange_thousands_separator = (string) $this->get_app_config_value('thousands_separator', ',');
		$exchange_decimal_point = (string) $this->get_app_config_value('decimal_point', '.');

		$this->db_gala->trans_begin();

		$sale_data = array(
			'sale_time' => date('Y-m-d H:i:s'),
			'customer_id' => $sales_customer_person_id,
			'employee_id' => $employee_id,
			'sold_by_employee_id' => $employee_id,
			'comment' => (string) $order['customer_note'],
			'discount_reason' => '',
			'show_comment_on_receipt' => 0,
			'rule_id' => NULL,
			'rule_discount' => NULL,
			'payment_type' => '',
			'cc_ref_no' => '',
			'auth_code' => NULL,
			'deleted_by' => NULL,
			'deleted' => 0,
			'suspended' => 1,
			'is_ecommerce' => 0,
			'ecommerce_order_id' => NULL,
			'ecommerce_status' => '',
			'store_account_payment' => 0,
			'was_layaway' => 0,
			'was_estimate' => 0,
			'location_id' => $location_id,
			'register_id' => NULL,
			'tier_id' => NULL,
			'points_used' => 0,
			'points_gained' => 0,
			'did_redeem_discount' => 0,
			'signature_image_id' => NULL,
			'deleted_taxes' => NULL,
			'total_quantity_purchased' => $total_quantity,
			'subtotal' => $subtotal,
			'tax' => $tax,
			'total' => $total,
			'profit' => $profit,
			'exchange_rate' => 1,
			'exchange_name' => '',
			'exchange_currency_symbol' => $exchange_symbol,
			'exchange_currency_symbol_location' => $exchange_symbol_location,
			'exchange_number_of_decimals' => $exchange_decimals,
			'exchange_thousands_separator' => $exchange_thousands_separator,
			'exchange_decimal_point' => $exchange_decimal_point,
			'is_purchase_points' => 0,
			'custom_field_1_value' => NULL,
			'custom_field_2_value' => NULL,
			'custom_field_3_value' => NULL,
			'custom_field_4_value' => NULL,
			'custom_field_5_value' => NULL,
			'custom_field_6_value' => NULL,
			'custom_field_7_value' => NULL,
			'custom_field_8_value' => NULL,
			'custom_field_9_value' => NULL,
			'custom_field_10_value' => NULL,
			'last_modified' => date('Y-m-d H:i:s'),
			'override_taxes' => NULL,
			'return_sale_id' => NULL,
			'tip' => NULL,
			'total_quantity_received' => 0
		);
		$this->db_gala->insert('phppos_sales', $sale_data);
		$sale_id = (int) $this->db_gala->insert_id();

		$line = 0;
		foreach ($items as $item)
		{
			$item_id = (int) $item['item_id'];
			$qty = (float) $item['quantity'];
			$unit_price = (float) $item['unit_price'];
			$line_subtotal = (float) $item['line_total'];
			$cost_price = isset($item_cost_map[$item_id]) ? (float) $item_cost_map[$item_id] : 0;
			$line_profit = $line_subtotal - ($cost_price * $qty);

			$this->db_gala->insert('phppos_sales_items', array(
				'sale_id' => $sale_id,
				'item_id' => $item_id,
				'item_variation_id' => NULL,
				'rule_id' => NULL,
				'rule_discount' => NULL,
				'description' => '',
				'serialnumber' => NULL,
				'line' => $line,
				'quantity_purchased' => $qty,
				'quantity_received' => 0,
				'item_cost_price' => $cost_price,
				'item_unit_price' => $unit_price,
				'regular_item_unit_price_at_time_of_sale' => $unit_price,
				'discount_percent' => 0,
				'commission' => 0,
				'subtotal' => $line_subtotal,
				'tax' => 0,
				'total' => $line_subtotal,
				'profit' => $line_profit,
				'tier_id' => NULL,
				'series_id' => NULL,
				'damaged_qty' => 0,
				'override_taxes' => NULL,
				'unit_quantity' => NULL,
				'items_quantity_units_id' => NULL,
				'loyalty_multiplier' => NULL
			));

			$line++;
		}

		if ($this->db_gala->trans_status() === FALSE)
		{
			$this->db_gala->trans_rollback();
			return array('ok' => FALSE, 'message' => 'No se pudo crear la venta suspendida en PHPPOS.');
		}
		$this->db_gala->trans_commit();

		$new_status = 'processed';
		if (isset($order['status']) && (string) $order['status'] === 'cliente_nuevo')
		{
			$new_status = 'cliente_nuevo_suspendido';
		}

		$this->db_catalog
			->where('id', (int) $order_id)
			->update('catalog_orders', array(
				'sale_id' => $sale_id,
				'status' => $new_status
			));

		return array('ok' => TRUE, 'sale_id' => $sale_id, 'already_processed' => FALSE);
	}

	public function delete_order($order_id)
	{
		return $this->db_catalog
			->where('id', (int) $order_id)
			->update('catalog_orders', array(
				'status' => 'deleted'
			));
	}

	public function get_image_file($file_id)
	{
		return $this->db_gala
			->select('file_name, file_data')
			->from('phppos_app_files')
			->where('file_id', (int) $file_id)
			->limit(1)
			->get()
			->row_array();
	}

	public function get_company_logo_file_id($location_id = NULL)
	{
		if (!empty($location_id))
		{
			$location = $this->db_gala
				->select('company_logo')
				->from('phppos_locations')
				->where('location_id', (int) $location_id)
				->limit(1)
				->get()
				->row_array();

			if (!empty($location['company_logo']) && (int) $location['company_logo'] > 0)
			{
				return (int) $location['company_logo'];
			}
		}

		$row = $this->db_gala
			->query("SELECT `value` FROM phppos_app_config WHERE `key` = 'company_logo' LIMIT 1")
			->row_array();

		if (!empty($row['value']) && (int) $row['value'] > 0)
		{
			return (int) $row['value'];
		}

		return NULL;
	}

	public function get_templates()
	{
		return $this->db_catalog
			->from('catalog_templates')
			->order_by('is_active', 'DESC')
			->order_by('id', 'DESC')
			->get()
			->result_array();
	}

	public function get_template($template_id)
	{
		return $this->db_catalog
			->from('catalog_templates')
			->where('id', (int) $template_id)
			->limit(1)
			->get()
			->row_array();
	}

	public function get_active_template()
	{
		return $this->db_catalog
			->from('catalog_templates')
			->where('is_active', 1)
			->order_by('id', 'DESC')
			->limit(1)
			->get()
			->row_array();
	}

	public function create_template($data)
	{
		$this->db_catalog->insert('catalog_templates', $data);
		return (int) $this->db_catalog->insert_id();
	}

	public function activate_template($template_id)
	{
		$this->db_catalog->trans_begin();
		$this->db_catalog->set('is_active', 0)->update('catalog_templates');
		$this->db_catalog->where('id', (int) $template_id)->update('catalog_templates', array('is_active' => 1));

		if ($this->db_catalog->trans_status() === FALSE)
		{
			$this->db_catalog->trans_rollback();
			return FALSE;
		}

		$this->db_catalog->trans_commit();
		return TRUE;
	}

	public function update_template($template_id, $data)
	{
		$this->db_catalog->where('id', (int) $template_id)->update('catalog_templates', $data);
		return $this->db_catalog->affected_rows() >= 0;
	}

	public function delete_template($template_id)
	{
		$template = $this->get_template((int) $template_id);
		if (!$template)
		{
			return array('ok' => FALSE, 'message' => 'Plantilla no encontrada.');
		}

		$total_templates = (int) $this->db_catalog->count_all('catalog_templates');
		if ($total_templates <= 1)
		{
			return array('ok' => FALSE, 'message' => 'Debe existir al menos una plantilla.');
		}

		$this->db_catalog->trans_begin();

		$this->db_catalog->where('id', (int) $template_id)->delete('catalog_templates');

		if ((int) $template['is_active'] === 1)
		{
			$fallback = $this->db_catalog
				->from('catalog_templates')
				->order_by('id', 'DESC')
				->limit(1)
				->get()
				->row_array();

			if ($fallback)
			{
				$this->db_catalog->set('is_active', 0)->update('catalog_templates');
				$this->db_catalog->where('id', (int) $fallback['id'])->update('catalog_templates', array('is_active' => 1));
			}
		}

		if ($this->db_catalog->trans_status() === FALSE)
		{
			$this->db_catalog->trans_rollback();
			return array('ok' => FALSE, 'message' => 'No se pudo eliminar la plantilla.');
		}

		$this->db_catalog->trans_commit();
		return array('ok' => TRUE);
	}

	public function get_setting($key, $default = NULL)
	{
		$row = $this->db_catalog
			->query("SELECT `value` FROM catalog_settings WHERE `key` = ? LIMIT 1", array($key))
			->row_array();

		if (!$row)
		{
			return $default;
		}

		return $row['value'];
	}

	public function set_setting($key, $value)
	{
		$exists = $this->db_catalog
			->query("SELECT `key` FROM catalog_settings WHERE `key` = ? LIMIT 1", array($key))
			->row_array();

		$data = array(
			'key' => $key,
			'value' => (string) $value,
			'updated_at' => date('Y-m-d H:i:s')
		);

		if ($exists)
		{
			$this->db_catalog->query(
				"UPDATE catalog_settings SET `value` = ?, updated_at = ? WHERE `key` = ?",
				array((string) $value, date('Y-m-d H:i:s'), $key)
			);
			return;
		}

		$this->db_catalog->insert('catalog_settings', $data);
	}

	public function get_category_order_rows($location_id)
	{
		$location_id = (int) $location_id;
		$sql = "
			SELECT
				c.id AS category_id,
				c.name AS category_name,
				COALESCE(cs.sort_order, 9999) AS sort_order,
				COUNT(DISTINCT i.item_id) AS total_items
			FROM phppos_categories c
			INNER JOIN phppos_items i
				ON i.category_id = c.id
				AND i.deleted = 0
				AND COALESCE(i.item_inactive, 0) = 0
				AND COALESCE(i.is_ecommerce, 1) = 1
			LEFT JOIN phppos_location_items li
				ON li.item_id = i.item_id
				AND li.location_id = ?
			LEFT JOIN (
				SELECT item_id, MIN(image_id) AS any_image
				FROM phppos_item_images
				GROUP BY item_id
			) img
				ON img.item_id = i.item_id
			LEFT JOIN catalogo.catalog_category_sort cs
				ON cs.category_id = c.id
			WHERE COALESCE(c.deleted, 0) = 0
				AND COALESCE(img.any_image, i.main_image_id) IS NOT NULL
				AND (li.quantity IS NULL OR li.quantity >= 0)
			GROUP BY c.id, c.name, cs.sort_order
			ORDER BY COALESCE(cs.sort_order, 9999) ASC, c.name ASC
		";

		return $this->db_gala->query($sql, array($location_id))->result_array();
	}

	public function save_category_order($category_orders)
	{
		if (!is_array($category_orders))
		{
			return;
		}

		foreach ($category_orders as $category_id => $sort_order)
		{
			$category_id = (int) $category_id;
			$sort_order = (int) $sort_order;
			if ($category_id <= 0)
			{
				continue;
			}

			$exists = $this->db_catalog
				->from('catalog_category_sort')
				->where('category_id', $category_id)
				->limit(1)
				->get()
				->row_array();

			$data = array(
				'category_id' => $category_id,
				'sort_order' => $sort_order,
				'updated_at' => date('Y-m-d H:i:s')
			);

			if ($exists)
			{
				$this->db_catalog->where('category_id', $category_id)->update('catalog_category_sort', $data);
			}
			else
			{
				$this->db_catalog->insert('catalog_category_sort', $data);
			}
		}
	}

	private function get_app_config_value($key, $default = '')
	{
		$row = $this->db_gala
			->query("SELECT `value` FROM phppos_app_config WHERE `key` = ? LIMIT 1", array($key))
			->row_array();

		if (!$row || !array_key_exists('value', $row))
		{
			return $default;
		}

		return (string) $row['value'];
	}
}
