-- Script para crear tablas de Duemint en TNA Office
-- Base de datos: office_clientes (o tnaoffice_clientes)
-- Ejecutar este script en la base de datos de TNA Office

-- Tabla de clientes Duemint
CREATE TABLE IF NOT EXISTS `cron_duemint_clients` (
  `id_empresa` int(11) NOT NULL,
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `taxId` varchar(50) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `region` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `zipcode` varchar(20) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `fax` varchar(50) DEFAULT NULL,
  `paymentTerm` int(11) DEFAULT NULL,
  `url` text,
  PRIMARY KEY (`id_empresa`,`id`),
  KEY `idx_taxId` (`taxId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabla de documentos Duemint
CREATE TABLE IF NOT EXISTS `cron_duemint_documents` (
  `id_empresa` int(11) NOT NULL,
  `id_document` int(11) NOT NULL,
  `clientTaxId` varchar(50) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `issueDate` date DEFAULT NULL,
  `dueDate` date DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `statusName` varchar(100) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `net` decimal(16,2) DEFAULT NULL,
  `taxes` decimal(16,2) DEFAULT NULL,
  `total` decimal(16,2) DEFAULT NULL,
  `paidAmount` decimal(16,2) DEFAULT NULL,
  `paidDate` date DEFAULT NULL,
  `amountDue` decimal(16,2) DEFAULT NULL,
  `amountCredit` decimal(16,2) DEFAULT NULL,
  `amountDebit` decimal(16,2) DEFAULT NULL,
  `purchaseOrder` varchar(100) DEFAULT NULL,
  `code` int(11) DEFAULT NULL,
  `url` text,
  `xml` text,
  `pdf` text,
  PRIMARY KEY (`id_empresa`,`id_document`),
  KEY `idx_clientTaxId` (`clientTaxId`),
  KEY `idx_number` (`number`),
  `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tabla de pagos Duemint (estructura exacta de producción)
CREATE TABLE IF NOT EXISTS `cron_duemint_payments` (
  `id_empresa` int(11) NOT NULL,
  `id_document` int(11) NOT NULL,
  `id_payment` int(11) NOT NULL,
  `createdAt` datetime DEFAULT NULL,
  `date` date DEFAULT NULL,
  `currency` varchar(30) DEFAULT NULL,
  `amount` decimal(16,2) DEFAULT NULL,
  `paymentMethod` varchar(30) DEFAULT NULL,
  `transactionCode` varchar(30) DEFAULT NULL,
  `documentNumber` varchar(30) DEFAULT NULL,
  `post_bsale` smallint(6) DEFAULT NULL,
  `fecha_post_bsale` datetime DEFAULT NULL,
  PRIMARY KEY (`id_empresa`,`id_document`,`id_payment`),
  KEY `idx_id_document` (`id_document`),
  KEY `idx_post_bsale` (`post_bsale`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- Verificar que la tabla de parámetros tenga el campo company_id
-- (Ya debería existir del script anterior, pero verificamos)
ALTER TABLE `cron_duemint_parametros` 
ADD COLUMN IF NOT EXISTS `company_id` varchar(100) DEFAULT NULL 
AFTER `access_token`;

-- Actualizar company_id para TNA Office si no está configurado
UPDATE `cron_duemint_parametros` 
SET `company_id` = '1361' 
WHERE `id_empresa` = 1 AND (`company_id` IS NULL OR `company_id` = '');