-- =====================================================================
-- dimersad_cartera_ventas
-- Esquema ERP: Cartera, Ventas y Recaudos
-- Motor destino: MariaDB (cPanel ea-php82)
-- =====================================================================

CREATE DATABASE IF NOT EXISTS dimersad_cartera_ventas
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE dimersad_cartera_ventas;

-- ---------------------------------------------------------------------
-- Catálogo de vendedores
-- ---------------------------------------------------------------------
CREATE TABLE vendedores (
  cod_vendedor  VARCHAR(10)  NOT NULL,
  nombre        VARCHAR(150) NOT NULL,
  direccion     VARCHAR(200) NULL,
  telefono      VARCHAR(30)  NULL,
  cod_regional  VARCHAR(10)  NULL,
  canal         VARCHAR(50)  NULL,
  creado_en     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (cod_vendedor),
  INDEX idx_vendedores_regional (cod_regional),
  INDEX idx_vendedores_canal (canal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Maestro de clientes
-- ---------------------------------------------------------------------
CREATE TABLE clientes (
  codigo_cliente  VARCHAR(10)  NOT NULL,
  nit             VARCHAR(20)  NULL,
  nombre          VARCHAR(200) NOT NULL,
  nombre_negocio  VARCHAR(200) NULL,
  direccion       VARCHAR(200) NULL,
  cod_vendedor    VARCHAR(10)  NULL,
  telefono        VARCHAR(30)  NULL,
  tipologia       VARCHAR(60)  NULL,
  ciudad          VARCHAR(100) NULL,
  barrio          VARCHAR(150) NULL,
  cod_regional    VARCHAR(10)  NULL,
  fecha_creado    DATE         NULL,
  ruta            VARCHAR(60)  NULL,
  dias_cartera    INT          NOT NULL DEFAULT 0 COMMENT 'Plazo de pago en dias (columna CARTERA origen)',
  creado_en       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (codigo_cliente),
  INDEX idx_clientes_vendedor (cod_vendedor),
  INDEX idx_clientes_nit (nit),
  INDEX idx_clientes_ciudad (ciudad),
  INDEX idx_clientes_tipologia (tipologia),
  INDEX idx_clientes_nombre (nombre),
  CONSTRAINT fk_clientes_vendedor FOREIGN KEY (cod_vendedor)
    REFERENCES vendedores (cod_vendedor) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Maestro de productos
-- ---------------------------------------------------------------------
CREATE TABLE productos (
  codigo           VARCHAR(15)  NOT NULL,
  titulo           VARCHAR(250) NOT NULL,
  unidad           VARCHAR(10)  NULL,
  iva              DECIMAL(5,2) NOT NULL DEFAULT 0,
  categoria        VARCHAR(100) NULL,
  subcategoria     VARCHAR(100) NULL,
  nombre_comercial VARCHAR(250) NULL,
  marca            VARCHAR(100) NULL,
  factor_kg        DECIMAL(12,6) NULL,
  gramaje          DECIMAL(12,2) NULL,
  creado_en        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (codigo),
  INDEX idx_productos_categoria (categoria),
  INDEX idx_productos_subcategoria (subcategoria),
  INDEX idx_productos_marca (marca),
  INDEX idx_productos_titulo (titulo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Facturas (encabezado, se consolida al importar ventas)
-- ---------------------------------------------------------------------
CREATE TABLE facturas (
  factura         VARCHAR(20)  NOT NULL,
  codigo_cliente  VARCHAR(10)  NULL,
  nit             VARCHAR(20)  NULL,
  fecha           DATE         NULL,
  cod_vendedor    VARCHAR(10)  NULL,
  cod_regional    VARCHAR(10)  NULL,
  valor_neto      DECIMAL(14,2) NOT NULL DEFAULT 0,
  valor_iva       DECIMAL(14,2) NOT NULL DEFAULT 0,
  valor_total     DECIMAL(14,2) NOT NULL DEFAULT 0,
  num_items       INT          NOT NULL DEFAULT 0,
  creado_en       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (factura),
  INDEX idx_facturas_cliente (codigo_cliente),
  INDEX idx_facturas_vendedor (cod_vendedor),
  INDEX idx_facturas_fecha (fecha),
  CONSTRAINT fk_facturas_cliente FOREIGN KEY (codigo_cliente)
    REFERENCES clientes (codigo_cliente) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_facturas_vendedor FOREIGN KEY (cod_vendedor)
    REFERENCES vendedores (cod_vendedor) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Ventas - detalle de líneas por factura
-- ---------------------------------------------------------------------
CREATE TABLE ventas_detalle (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  factura          VARCHAR(20)  NOT NULL,
  codigo_cliente   VARCHAR(10)  NULL,
  codigo_producto  VARCHAR(15)  NULL,
  fecha            DATE         NULL,
  cantidad         DECIMAL(12,2) NOT NULL DEFAULT 0,
  precio           DECIMAL(14,2) NOT NULL DEFAULT 0,
  valor_neto       DECIMAL(14,2) NOT NULL DEFAULT 0,
  tarifa_iva       DECIMAL(5,2)  NOT NULL DEFAULT 0,
  valor_iva        DECIMAL(14,2) NOT NULL DEFAULT 0,
  cod_vendedor     VARCHAR(10)  NULL,
  cod_regional     VARCHAR(10)  NULL,
  linea_hash       CHAR(40)     NOT NULL COMMENT 'SHA1(factura|producto|cantidad|precio|fecha) para evitar duplicados al reimportar CSV',
  creado_en        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ventas_linea (linea_hash),
  INDEX idx_ventas_factura (factura),
  INDEX idx_ventas_cliente (codigo_cliente),
  INDEX idx_ventas_producto (codigo_producto),
  INDEX idx_ventas_vendedor (cod_vendedor),
  INDEX idx_ventas_fecha (fecha),
  CONSTRAINT fk_ventas_factura FOREIGN KEY (factura)
    REFERENCES facturas (factura) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_ventas_cliente FOREIGN KEY (codigo_cliente)
    REFERENCES clientes (codigo_cliente) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_ventas_producto FOREIGN KEY (codigo_producto)
    REFERENCES productos (codigo) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Cartera - saldos vigentes por factura (snapshot mas reciente cargado)
-- ---------------------------------------------------------------------
CREATE TABLE cartera (
  factura           VARCHAR(20)  NOT NULL,
  codigo_cliente    VARCHAR(10)  NULL,
  cod_vendedor      VARCHAR(10)  NULL,
  valor_documento   DECIMAL(14,2) NOT NULL DEFAULT 0,
  saldo             DECIMAL(14,2) NOT NULL DEFAULT 0,
  fecha_factura     DATE         NULL,
  dias_vencidos     INT          NOT NULL DEFAULT 0,
  retefuente        DECIMAL(14,2) NOT NULL DEFAULT 0,
  descuento         DECIMAL(14,2) NOT NULL DEFAULT 0,
  actualizado_en    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (factura),
  INDEX idx_cartera_cliente (codigo_cliente),
  INDEX idx_cartera_vendedor (cod_vendedor),
  INDEX idx_cartera_dias (dias_vencidos),
  CONSTRAINT fk_cartera_cliente FOREIGN KEY (codigo_cliente)
    REFERENCES clientes (codigo_cliente) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_cartera_vendedor FOREIGN KEY (cod_vendedor)
    REFERENCES vendedores (cod_vendedor) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Recaudos - pagos aplicados a facturas
-- ---------------------------------------------------------------------
CREATE TABLE recaudos (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  fecha_recaudo    DATE         NOT NULL,
  codigo_cliente   VARCHAR(10)  NULL,
  nombre_cliente   VARCHAR(200) NULL,
  cod_vendedor     VARCHAR(10)  NULL,
  factura          VARCHAR(20)  NULL,
  valor_recaudo    DECIMAL(14,2) NOT NULL DEFAULT 0,
  id_tipo_pago     VARCHAR(10)  NULL,
  tipo_pago        VARCHAR(60)  NULL,
  retefuente       DECIMAL(14,2) NOT NULL DEFAULT 0,
  descuento        DECIMAL(14,2) NOT NULL DEFAULT 0,
  linea_hash       CHAR(40)     NOT NULL COMMENT 'SHA1(fecha|cliente|factura|valor) para evitar duplicados al reimportar CSV',
  creado_en        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_recaudos_linea (linea_hash),
  INDEX idx_recaudos_cliente (codigo_cliente),
  INDEX idx_recaudos_vendedor (cod_vendedor),
  INDEX idx_recaudos_factura (factura),
  INDEX idx_recaudos_fecha (fecha_recaudo),
  CONSTRAINT fk_recaudos_cliente FOREIGN KEY (codigo_cliente)
    REFERENCES clientes (codigo_cliente) ON UPDATE CASCADE ON DELETE SET NULL,
  CONSTRAINT fk_recaudos_vendedor FOREIGN KEY (cod_vendedor)
    REFERENCES vendedores (cod_vendedor) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Bitácora de importaciones (modulo Importacion)
-- ---------------------------------------------------------------------
CREATE TABLE importaciones_log (
  id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tipo             ENUM('clientes','productos','vendedores','ventas','cartera','recaudos') NOT NULL,
  archivo          VARCHAR(255) NOT NULL,
  filas_leidas     INT NOT NULL DEFAULT 0,
  filas_insertadas INT NOT NULL DEFAULT 0,
  filas_actualizadas INT NOT NULL DEFAULT 0,
  filas_error      INT NOT NULL DEFAULT 0,
  detalle_errores  TEXT NULL,
  usuario          VARCHAR(100) NULL,
  creado_en        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  INDEX idx_import_tipo (tipo),
  INDEX idx_import_fecha (creado_en)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
