-- Cuentas por Pagar Guatemala – Esquema base ampliado
-- Motor: MySQL 8.0, InnoDB, utf8mb4
-- Nota: usa este archivo para crear/ajustar la estructura. No colocar secretos.

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- =====================================================================
-- 1) Catálogos y parámetros
-- =====================================================================

CREATE TABLE IF NOT EXISTS monedas (
  codigo       VARCHAR(3)  PRIMARY KEY,        -- GTQ, USD
  nombre       VARCHAR(50) NOT NULL,
  simbolo      VARCHAR(5)  NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO monedas (codigo, nombre, simbolo) VALUES
 ('GTQ','Quetzal','Q'),
 ('USD','Dólar','$');

CREATE TABLE IF NOT EXISTS tipos_cambio (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  fecha        DATE NOT NULL,
  moneda       VARCHAR(3) NOT NULL,
  tasa         DECIMAL(12,6) NOT NULL,
  UNIQUE KEY uq_tc (fecha, moneda),
  CONSTRAINT fk_tc_moneda FOREIGN KEY (moneda) REFERENCES monedas(codigo)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tipos de documento de proveedor (cargos/abonos)
CREATE TABLE IF NOT EXISTS cat_tipos_documento (
  codigo       VARCHAR(10) PRIMARY KEY,  -- FA, FE, ND, NC, CH, TL, PAGRE, RET, RET_IVA
  nombre       VARCHAR(120) NOT NULL,
  naturaleza   ENUM('cargo','abono','neutro') NOT NULL DEFAULT 'cargo'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO cat_tipos_documento (codigo, nombre, naturaleza) VALUES
 ('FA','Factura Proveedor','cargo'),
 ('ND','Nota de Débito','cargo'),
 ('NC','Nota de Crédito','abono'),
 ('CH','Cheque','abono'),
 ('TR','Transferencia','abono'),
 ('PP','Pagaré','abono'),
 ('RET_ISR','Retención ISR','abono'),
 ('RET_IVA','Retención IVA','abono'),
 ('FE','Factura del Exterior','cargo');

-- Clasificación de proveedores
CREATE TABLE IF NOT EXISTS cat_clasif_proveedor (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo       VARCHAR(30) NOT NULL UNIQUE,  -- bienes, servicios, exterior, bancario, combustible, etc.
  nombre       VARCHAR(120) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO cat_clasif_proveedor (codigo,nombre) VALUES
 ('bienes','Proveedor de bienes'),
 ('servicios','Proveedor de servicios'),
 ('exterior','Proveedor del exterior'),
 ('bancario','Institución bancaria'),
 ('combustible','Proveedor de combustible');

-- Centros de costo
CREATE TABLE IF NOT EXISTS centros_costo (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id   BIGINT UNSIGNED NOT NULL,
  codigo       VARCHAR(50) NOT NULL,
  nombre       VARCHAR(120) NOT NULL,
  esta_activo  TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_cc (cliente_id,codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Bancos y cuentas bancarias
CREATE TABLE IF NOT EXISTS bancos (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id   BIGINT UNSIGNED NOT NULL,
  nombre       VARCHAR(150) NOT NULL,
  sigla        VARCHAR(20)  NULL,
  esta_activo  TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cuentas_bancarias (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id       BIGINT UNSIGNED NOT NULL,
  banco_id         BIGINT UNSIGNED NOT NULL,
  nombre           VARCHAR(120) NOT NULL,
  numero_cuenta    VARCHAR(120) NOT NULL,
  moneda           VARCHAR(3) NOT NULL DEFAULT 'GTQ',
  esta_activo      TINYINT(1) DEFAULT 1,
  CONSTRAINT fk_cb_banco FOREIGN KEY (banco_id) REFERENCES bancos(id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Impuestos parametrizables (IVA, ISR, IDP, Timbres, etc.)
CREATE TABLE IF NOT EXISTS cat_impuestos (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo        VARCHAR(30) NOT NULL UNIQUE,  -- IVA, ISR, IDP_SUPER, IDP_REGULAR, IDP_DIESEL, DUCA_12, etc.
  nombre        VARCHAR(150) NOT NULL,
  tipo_calculo  ENUM('porcentaje','monto_fijo_unidad') NOT NULL DEFAULT 'porcentaje',
  unidad_base   ENUM('monto','galon','unidad') NOT NULL DEFAULT 'monto',
  porcentaje    DECIMAL(10,4) NULL,
  monto_fijo    DECIMAL(14,6) NULL,
  aplica_en     ENUM('item','factura') NOT NULL DEFAULT 'factura',
  vigente_desde DATE NULL,
  vigente_hasta DATE NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO cat_impuestos (codigo, nombre, tipo_calculo, unidad_base, porcentaje, monto_fijo, aplica_en)
VALUES
 ('IVA','Impuesto al Valor Agregado', 'porcentaje','monto', 0.12, NULL, 'factura'),
 ('RET_ISR_5','Retención ISR 5%',        'porcentaje','monto', 0.05, NULL, 'factura'),
 ('RET_ISR_7','Retención ISR 7% (> Q30k)','porcentaje','monto', 0.07, NULL, 'factura'),
 ('RET_NO_DOM','Retención a No Domiciliado','porcentaje','monto', 0.05, NULL, 'factura'),
 ('IDP_SUPER','Impuesto Distribución Petróleo (Súper)','monto_fijo_unidad','galon', NULL, 4.70, 'item'),
 ('IDP_REG','Impuesto Distribución Petróleo (Regular)','monto_fijo_unidad','galon', NULL, 4.60, 'item'),
 ('IDP_DIESEL','Impuesto Distribución Petróleo (Diésel)','monto_fijo_unidad','galon', NULL, 1.30, 'item');

-- =====================================================================
-- 2) Ajustes de tablas existentes (compatibles con el código actual)
-- =====================================================================

-- Proveedores: enriquecer para reglas fiscales locales
ALTER TABLE proveedores
  ADD COLUMN IF NOT EXISTS tipo_proveedor ENUM('bienes','servicios','exterior','bancario','combustible') NULL AFTER nit,
  ADD COLUMN IF NOT EXISTS dias_credito INT DEFAULT 0 AFTER condiciones_pago,
  ADD COLUMN IF NOT EXISTS es_agente_retencion_iva TINYINT(1) DEFAULT 0 AFTER dias_credito,
  ADD COLUMN IF NOT EXISTS es_afecto_ret_isr TINYINT(1) DEFAULT 1 AFTER es_agente_retencion_iva,
  ADD COLUMN IF NOT EXISTS clasif_id BIGINT UNSIGNED NULL AFTER es_afecto_ret_isr,
  ADD CONSTRAINT fk_proveedor_clasif FOREIGN KEY (clasif_id) REFERENCES cat_clasif_proveedor(id)
    ON UPDATE CASCADE ON DELETE SET NULL;

-- Facturas: permitir CC y normalizar impuestos a nivel cabecera
ALTER TABLE facturas
  ADD COLUMN IF NOT EXISTS centro_costo_id BIGINT UNSIGNED NULL AFTER proveedor_id,
  ADD COLUMN IF NOT EXISTS tipo_cambio DECIMAL(12,6) NULL AFTER moneda,
  ADD COLUMN IF NOT EXISTS referencia_externa VARCHAR(150) NULL AFTER numero_documento,
  ADD CONSTRAINT fk_fact_cc FOREIGN KEY (centro_costo_id) REFERENCES centros_costo(id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  ADD INDEX IF NOT EXISTS idx_fact_cliente_estado (cliente_id, estado),
  ADD INDEX IF NOT EXISTS idx_fact_proveedor (proveedor_id),
  ADD INDEX IF NOT EXISTS idx_fact_vencimiento (fecha_vencimiento);

-- Pagos: convertir a encabezado (factura_id opcional) y permitir moneda/cta bancaria
ALTER TABLE pagos
  ADD COLUMN IF NOT EXISTS proveedor_id BIGINT UNSIGNED NULL AFTER factura_id,
  ADD COLUMN IF NOT EXISTS cuenta_bancaria_id BIGINT UNSIGNED NULL AFTER banco_id,
  ADD COLUMN IF NOT EXISTS moneda VARCHAR(3) NULL AFTER monto,
  ADD CONSTRAINT fk_pago_proveedor FOREIGN KEY (proveedor_id) REFERENCES proveedores(id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  ADD CONSTRAINT fk_pago_cta FOREIGN KEY (cuenta_bancaria_id) REFERENCES cuentas_bancarias(id)
    ON UPDATE CASCADE ON DELETE SET NULL,
  ADD INDEX IF NOT EXISTS idx_pago_fecha (fecha_pago),
  ADD INDEX IF NOT EXISTS idx_pago_factura (factura_id);

-- Relación N a N para aplicaciones de abonos (pagos/NC/retenciones) a facturas
CREATE TABLE IF NOT EXISTS aplicaciones (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id      BIGINT UNSIGNED NULL,
  proveedor_id    BIGINT UNSIGNED NULL,
  factura_id      BIGINT UNSIGNED NOT NULL,
  origen_tipo     ENUM('pago','nota_credito','retencion','ajuste') NOT NULL DEFAULT 'pago',
  origen_id       BIGINT UNSIGNED NULL, -- id del pago/nota/retención según origen_tipo
  monto_aplicado  DECIMAL(14,2) NOT NULL,
  fecha_aplicacion DATETIME NOT NULL,
  comentario      VARCHAR(255) NULL,
  esta_activo     TINYINT(1) DEFAULT 1,
  creado_en       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  actualizado_en  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_apl_factura FOREIGN KEY (factura_id) REFERENCES facturas(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  INDEX idx_apl_factura (factura_id),
  INDEX idx_apl_proveedor (proveedor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Notas de proveedor (crédito/débito) como documentos de abono/cargo
CREATE TABLE IF NOT EXISTS notas_proveedor (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id     BIGINT UNSIGNED NOT NULL,
  proveedor_id   BIGINT UNSIGNED NOT NULL,
  tipo           ENUM('nota_credito','nota_debito') NOT NULL,
  serie          VARCHAR(25) NULL,
  numero         VARCHAR(100) NOT NULL,
  fecha_emision  DATE NOT NULL,
  moneda         VARCHAR(3) NOT NULL DEFAULT 'GTQ',
  total          DECIMAL(14,2) NOT NULL,
  observaciones  TEXT NULL,
  esta_activo    TINYINT(1) DEFAULT 1,
  creado_en      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  actualizado_en TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  eliminado_en   TIMESTAMP NULL,
  UNIQUE KEY uq_np (cliente_id, proveedor_id, tipo, serie, numero),
  CONSTRAINT fk_np_proveedor FOREIGN KEY (proveedor_id) REFERENCES proveedores(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Retenciones emitidas (documento fiscal)
CREATE TABLE IF NOT EXISTS retenciones (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id      BIGINT UNSIGNED NOT NULL,
  proveedor_id    BIGINT UNSIGNED NOT NULL,
  factura_id      BIGINT UNSIGNED NULL, -- puede asociarse en la aplicación
  impuesto_id     BIGINT UNSIGNED NOT NULL, -- cat_impuestos (RET_ISR*, RET_IVA, RET_NO_DOM)
  porcentaje      DECIMAL(10,4) NULL,
  base_imponible  DECIMAL(14,2) NOT NULL,
  monto_retenido  DECIMAL(14,2) NOT NULL,
  numero_doc      VARCHAR(120) NULL,
  fecha_emision   DATE NOT NULL,
  observaciones   TEXT NULL,
  esta_activo     TINYINT(1) DEFAULT 1,
  creado_en       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  actualizado_en  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  eliminado_en    TIMESTAMP NULL,
  CONSTRAINT fk_ret_imp FOREIGN KEY (impuesto_id) REFERENCES cat_impuestos(id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_ret_proveedor FOREIGN KEY (proveedor_id) REFERENCES proveedores(id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Impuestos calculados a nivel factura (para ISR, IVA retenido, etc.)
CREATE TABLE IF NOT EXISTS facturas_impuestos (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  factura_id     BIGINT UNSIGNED NOT NULL,
  impuesto_id    BIGINT UNSIGNED NOT NULL,
  base_imponible DECIMAL(14,2) NOT NULL,
  porcentaje     DECIMAL(10,4) NULL,
  monto          DECIMAL(14,2) NOT NULL,
  CONSTRAINT fk_fi_fact FOREIGN KEY (factura_id) REFERENCES facturas(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_fi_imp FOREIGN KEY (impuesto_id) REFERENCES cat_impuestos(id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX idx_fi_factura (factura_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Relación factura-centro de costo (si se requiere N a N)
CREATE TABLE IF NOT EXISTS facturas_centros_costo (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  factura_id     BIGINT UNSIGNED NOT NULL,
  centro_costo_id BIGINT UNSIGNED NOT NULL,
  porcentaje     DECIMAL(6,3) NULL,  -- reparto opcional
  CONSTRAINT fk_fcc_fact FOREIGN KEY (factura_id) REFERENCES facturas(id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_fcc_cc FOREIGN KEY (centro_costo_id) REFERENCES centros_costo(id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  UNIQUE KEY uq_fcc (factura_id, centro_costo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 3) Vistas operativas (saldos y aging)
-- =====================================================================

-- Suma de aplicaciones (incluye pagos, notas, retenciones)
CREATE OR REPLACE VIEW v_facturas_aplicaciones AS
SELECT
  f.id               AS factura_id,
  COALESCE(SUM(CASE WHEN a.esta_activo = 1 THEN a.monto_aplicado ELSE 0 END),0) AS total_aplicado
FROM facturas f
LEFT JOIN aplicaciones a ON a.factura_id = f.id AND a.esta_activo = 1
GROUP BY f.id;

-- Saldos calculados (no depende de la columna saldo)
CREATE OR REPLACE VIEW v_facturas_saldos AS
SELECT
  f.id,
  f.cliente_id,
  f.proveedor_id,
  f.numero_documento,
  f.serie,
  f.fecha_emision,
  f.fecha_vencimiento,
  f.moneda,
  f.total,
  COALESCE(vfa.total_aplicado,0) AS total_aplicado,
  ROUND(f.total - COALESCE(vfa.total_aplicado,0), 2) AS saldo_calculado,
  CASE
    WHEN ROUND(f.total - COALESCE(vfa.total_aplicado,0), 2) <= 0 THEN 'pagada'
    WHEN COALESCE(vfa.total_aplicado,0) > 0 THEN 'parcial'
    ELSE 'pendiente'
  END AS estado_calculado
FROM facturas f
LEFT JOIN v_facturas_aplicaciones vfa ON vfa.factura_id = f.id
WHERE f.esta_activo = 1;

-- Aging por proveedor
CREATE OR REPLACE VIEW v_aging_proveedores AS
SELECT
  f.proveedor_id,
  p.nombre AS proveedor,
  SUM(CASE WHEN DATEDIFF(CURDATE(), f.fecha_vencimiento) <= 0 THEN vfs.saldo_calculado ELSE 0 END) AS no_vencido,
  SUM(CASE WHEN DATEDIFF(CURDATE(), f.fecha_vencimiento) BETWEEN 1 AND 30 THEN vfs.saldo_calculado ELSE 0 END) AS d01_30,
  SUM(CASE WHEN DATEDIFF(CURDATE(), f.fecha_vencimiento) BETWEEN 31 AND 60 THEN vfs.saldo_calculado ELSE 0 END) AS d31_60,
  SUM(CASE WHEN DATEDIFF(CURDATE(), f.fecha_vencimiento) BETWEEN 61 AND 90 THEN vfs.saldo_calculado ELSE 0 END) AS d61_90,
  SUM(CASE WHEN DATEDIFF(CURDATE(), f.fecha_vencimiento) > 90 THEN vfs.saldo_calculado ELSE 0 END) AS d90_mas,
  SUM(vfs.saldo_calculado) AS saldo_total
FROM v_facturas_saldos vfs
JOIN facturas f ON f.id = vfs.id
JOIN proveedores p ON p.id = f.proveedor_id
GROUP BY f.proveedor_id, p.nombre;

-- =====================================================================
-- 4) Índices recomendados
-- =====================================================================

CREATE INDEX IF NOT EXISTS idx_proveedor_cliente ON proveedores (cliente_id, esta_activo);
CREATE INDEX IF NOT EXISTS idx_docs_factura ON documentos (factura_id, esta_activo);
CREATE INDEX IF NOT EXISTS idx_det_factura ON detalles_factura (factura_id, esta_activo);

-- =====================================================================
-- 5) Tablas de staging (cargas masivas)
-- =====================================================================

CREATE TABLE IF NOT EXISTS stg_cargos (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id       BIGINT UNSIGNED NULL,
  proveedor_nit    VARCHAR(20)  NULL,
  proveedor_nombre VARCHAR(150) NULL,
  proveedor_id     BIGINT UNSIGNED NULL,
  tipo_doc         VARCHAR(10)  NULL,  -- FA/FE/ND
  serie            VARCHAR(25)  NULL,
  numero           VARCHAR(100) NULL,
  fecha_emision    DATE NULL,
  fecha_venc       DATE NULL,
  descripcion      VARCHAR(255) NULL,
  moneda           VARCHAR(3)   NULL,
  subtotal         DECIMAL(14,2) NULL,
  iva              DECIMAL(14,2) NULL,
  isr              DECIMAL(14,2) NULL,
  total            DECIMAL(14,2) NULL,
  centro_costo     VARCHAR(50)  NULL,
  estado           ENUM('pendiente','validado','cargado','error') DEFAULT 'pendiente',
  error_msg        VARCHAR(255) NULL,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stg_abonos (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id       BIGINT UNSIGNED NULL,
  proveedor_nit    VARCHAR(20)  NULL,
  proveedor_nombre VARCHAR(150) NULL,
  proveedor_id     BIGINT UNSIGNED NULL,
  tipo_abono       VARCHAR(20)  NULL,  -- TR/CH/NC/RET_ISR/RET_IVA
  referencia       VARCHAR(120) NULL,
  fecha            DATE NULL,
  moneda           VARCHAR(3)   NULL,
  monto            DECIMAL(14,2) NULL,
  estado           ENUM('pendiente','validado','cargado','error') DEFAULT 'pendiente',
  error_msg        VARCHAR(255) NULL,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS stg_aplicaciones (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id       BIGINT UNSIGNED NULL,
  proveedor_id     BIGINT UNSIGNED NULL,
  factura_serie    VARCHAR(25)  NULL,
  factura_numero   VARCHAR(100) NULL,
  abono_ref        VARCHAR(120) NULL,  -- referencia del pago/NC/ret
  origen_tipo      VARCHAR(20)  NULL,  -- pago/nota_credito/retencion
  origen_id        BIGINT UNSIGNED NULL,
  monto_aplicar    DECIMAL(14,2) NULL,
  estado           ENUM('pendiente','validado','cargado','error') DEFAULT 'pendiente',
  error_msg        VARCHAR(255) NULL,
  created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Fin del esquema
