-- =============================================================
-- Module: Cierre (Cash Closeout)
-- Adds module, permissions, and required tables into the PHPPOS DB
-- =============================================================

-- 1) Module registration
INSERT IGNORE INTO phppos_modules (name_lang_key, desc_lang_key, sort, icon, module_id)
VALUES ('module_cierre','module_cierre_desc',72,'icon ti-clipboard','cierre');

-- 2) Module actions
INSERT IGNORE INTO phppos_modules_actions (action_id, module_id, action_name_key, sort) VALUES
('view','cierre','cierre_view',10),
('add_update','cierre','cierre_add_update',20),
('complete','cierre','cierre_complete',30),
('config','cierre','cierre_config',40);

-- 3) Default permission for admin (person_id=1)
INSERT IGNORE INTO phppos_permissions (module_id, person_id) VALUES ('cierre',1);
INSERT IGNORE INTO phppos_permissions_actions (module_id, person_id, action_id) VALUES
('cierre',1,'view'),
('cierre',1,'add_update'),
('cierre',1,'complete'),
('cierre',1,'config');

-- 4) Tables
CREATE TABLE IF NOT EXISTS phppos_cierre_config_general (
  id INT NOT NULL PRIMARY KEY,
  margen_error_usd DECIMAL(10,2) NOT NULL DEFAULT 5.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS phppos_cierre_config_cajas (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  person_id INT(10) NOT NULL,
  alias VARCHAR(80) NOT NULL DEFAULT '',
  activo TINYINT(1) NOT NULL DEFAULT 1,
  orden INT NOT NULL DEFAULT 0,
  UNIQUE KEY uk_caja_person (person_id),
  CONSTRAINT fk_cierre_cfg_cajas_employee FOREIGN KEY (person_id) REFERENCES phppos_employees(person_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS phppos_cierre_config_pago (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(100) NOT NULL,
  moneda_base ENUM('USD','BS') NOT NULL DEFAULT 'USD',
  contar_en_total TINYINT(1) NOT NULL DEFAULT 1,
  source VARCHAR(20) NOT NULL DEFAULT 'phppos',
  last_seen_at DATETIME NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  orden INT NOT NULL DEFAULT 0,
  UNIQUE KEY uk_pago_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS phppos_cierres (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  fecha DATE NOT NULL,
  caja_person_id INT(10) NOT NULL,
  bolsa_manual VARCHAR(50) NOT NULL,
  cambio_tasa DECIMAL(12,4) NOT NULL DEFAULT 1.0000 COMMENT 'Moneda local por 1 USD (si aplica)',
  notas TEXT NULL,
  estado ENUM('Pendiente','Completado','Revision','CompletadoRevisado') NOT NULL DEFAULT 'Pendiente',
  created_by INT(10) NULL,
  completed_by INT(10) NULL,
  completed_at DATETIME NULL,
  reviewed_by INT(10) NULL,
  reviewed_at DATETIME NULL,
  total_sistema_usd DECIMAL(14,2) NOT NULL DEFAULT 0,
  total_contado_usd DECIMAL(14,2) NOT NULL DEFAULT 0,
  diferencia_total_usd DECIMAL(14,2) NOT NULL DEFAULT 0,
  completo TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_cierre (fecha, caja_person_id, bolsa_manual),
  KEY idx_caja (caja_person_id),
  KEY idx_created_by (created_by),
  CONSTRAINT fk_cierres_caja_employee FOREIGN KEY (caja_person_id) REFERENCES phppos_employees(person_id) ON DELETE RESTRICT,
  CONSTRAINT fk_cierres_created_by FOREIGN KEY (created_by) REFERENCES phppos_employees(person_id) ON DELETE SET NULL,
  CONSTRAINT fk_cierres_completed_by FOREIGN KEY (completed_by) REFERENCES phppos_employees(person_id) ON DELETE SET NULL,
  CONSTRAINT fk_cierres_reviewed_by FOREIGN KEY (reviewed_by) REFERENCES phppos_employees(person_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS phppos_cierre_detalle (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  cierre_id INT NOT NULL,
  tipo_pago VARCHAR(100) NOT NULL,
  moneda ENUM('USD','BS') NOT NULL DEFAULT 'USD',
  monto_sistema_usd DECIMAL(14,2) NOT NULL DEFAULT 0,
  monto_contado DECIMAL(14,2) NOT NULL DEFAULT 0,
  monto_contado_usd DECIMAL(14,2) NOT NULL DEFAULT 0,
  diferencia_usd DECIMAL(14,2) NOT NULL DEFAULT 0,
  es_adicional TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_cierre (cierre_id),
  CONSTRAINT fk_cierre_detalle_cierre FOREIGN KEY (cierre_id) REFERENCES phppos_cierres(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 5) Seed general config
INSERT INTO phppos_cierre_config_general (id, margen_error_usd)
VALUES (1, 5.00)
ON DUPLICATE KEY UPDATE margen_error_usd = VALUES(margen_error_usd);

-- 6) Seed payment types from existing PHPPOS sales payments
INSERT IGNORE INTO phppos_cierre_config_pago (nombre, moneda_base, contar_en_total, source, last_seen_at, activo, orden)
SELECT 
  p.payment_type AS nombre,
  'USD' AS moneda_base,
  1 AS contar_en_total,
  'phppos' AS source,
  NOW() AS last_seen_at,
  1 AS activo,
  0 AS orden
FROM (SELECT DISTINCT payment_type FROM phppos_sales_payments) p;
