--
-- Database: `core_accounting`
--

-- --------------------------------------------------------

--
-- Table structure for table `core_accounting_auxiliary`
--

CREATE TABLE `core_accounting_auxiliary` (
  `relative` varchar(50) NOT NULL,
  `users_sid` varchar(50) NOT NULL,
  `global_branch_office_id` varchar(50) NOT NULL,
  `origin_type` enum('LOCAL','EXTERNAL') NOT NULL DEFAULT 'LOCAL',
  `source_module` enum('CORE','CUSTOMERS','SUPPLIER','SIVIR','ACCOUNTING','SISTEMA_EXTERNO') NOT NULL,
  `source_entity` enum('PERSONA','USUARIO','CLIENTE','PROVEEDOR','PRODUCTO','VENTA','COMPRA','FACTURA','OTROS') NOT NULL,
  `external_relative` varchar(50) DEFAULT NULL COMMENT 'ID de la entidad en su modulo original',
  `label` varchar(255) NOT NULL,
  `status` varchar(2) NOT NULL DEFAULT 'A',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `core_accounting_entry`
--

CREATE TABLE `core_accounting_entry` (
  `relative` varchar(50) NOT NULL,
  `users_sid` varchar(50) NOT NULL,
  `global_branch_office_id` varchar(50) NOT NULL,
  `accounting_period_id` varchar(50) NOT NULL,
  `entry_type` enum('INGRESO','EGRESO','TRASPASO','APERTURA','AJUSTE') NOT NULL,
  `entry_number` varchar(100) NOT NULL COMMENT 'Correlativo por tipo y periodo.\r\nEJ: E-2026-0127-[N]',
  `entry_date` date NOT NULL,
  `currency_id` int NOT NULL DEFAULT '1' COMMENT 'related to currency table',
  `currency_rate` decimal(12,6) NOT NULL COMMENT 'Tipo de cambio (ej. vs USD)',
  `ufv_rate` decimal(12,6) DEFAULT NULL COMMENT 'Valor UFV del día para ajustes NIC 29',
  `gloss` text NOT NULL COMMENT 'Resumen de la operación',
  `document_reference` varchar(100) DEFAULT NULL COMMENT 'Nro de factura o documento externo',
  `status` varchar(2) NOT NULL DEFAULT 'A',
  `created_by` varchar(50) NOT NULL COMMENT 'Relacionado con global_users',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `core_accounting_entry_details`
--

CREATE TABLE `core_accounting_entry_details` (
  `id` bigint NOT NULL,
  `entry_relative` varchar(50) NOT NULL,
  `account_relative` varchar(50) NOT NULL,
  `auxiliary_id` varchar(50) DEFAULT NULL,
  `debit` decimal(18,2) DEFAULT '0.00',
  `credit` decimal(18,2) DEFAULT '0.00',
  `debit_foreign` decimal(18,2) DEFAULT '0.00' COMMENT 'Monto en moneda extranjera NIC 21',
  `credit_foreign` decimal(18,2) DEFAULT '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `core_accounting_period`
--

CREATE TABLE `core_accounting_period` (
  `relative` varchar(50) NOT NULL,
  `users_sid` varchar(50) NOT NULL,
  `global_branch_office_id` varchar(50) NOT NULL,
  `year` int NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `is_closed` tinyint(1) DEFAULT '0' COMMENT '0=Abierto, 1=Cerrado',
  `status` varchar(2) NOT NULL DEFAULT 'A',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `core_accounting_plan`
--

CREATE TABLE `core_accounting_plan` (
  `relative` varchar(50) NOT NULL,
  `users_sid` varchar(50) NOT NULL,
  `global_branch_office_id` varchar(50) NOT NULL,
  `parent_relative` varchar(50) DEFAULT NULL COMMENT 'Relación jerárquica a la cuenta padre',
  `code` varchar(25) NOT NULL COMMENT 'Ejemplo: 1.1.1.01.001',
  `label` varchar(250) NOT NULL,
  `level` int NOT NULL COMMENT 'Nivel en el árbol contable',
  `account_type` enum('ACTIVO','PASIVO','PATRIMONIO','INGRESO','EGRESO','ORDEN') NOT NULL,
  `nature` enum('DEUDOR','ACREEDOR') NOT NULL,
  `is_selectable` tinyint(1) DEFAULT '0' COMMENT 'Indica si se puede registrar asientos en esta cuenta',
  `status` varchar(2) NOT NULL DEFAULT 'A'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `core_accounting_auxiliary`
--
ALTER TABLE `core_accounting_auxiliary`
  ADD PRIMARY KEY (`relative`),
  ADD KEY `idx_auxiliary_lookup` (`source_module`,`source_entity`,`external_relative`);

--
-- Indexes for table `core_accounting_entry`
--
ALTER TABLE `core_accounting_entry`
  ADD PRIMARY KEY (`relative`),
  ADD KEY `accounting_period_id` (`accounting_period_id`);

--
-- Indexes for table `core_accounting_entry_details`
--
ALTER TABLE `core_accounting_entry_details`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_entry_details_auxiliary` (`auxiliary_id`),
  ADD KEY `entry_relative` (`entry_relative`),
  ADD KEY `account_relative` (`account_relative`);

--
-- Indexes for table `core_accounting_period`
--
ALTER TABLE `core_accounting_period`
  ADD PRIMARY KEY (`relative`);

--
-- Indexes for table `core_accounting_plan`
--
ALTER TABLE `core_accounting_plan`
  ADD PRIMARY KEY (`relative`),
  ADD KEY `code` (`code`),
  ADD KEY `parent_relative` (`parent_relative`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `core_accounting_entry_details`
--
ALTER TABLE `core_accounting_entry_details`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `core_accounting_entry`
--
ALTER TABLE `core_accounting_entry`
  ADD CONSTRAINT `core_accounting_entry_ibfk_1` FOREIGN KEY (`accounting_period_id`) REFERENCES `core_accounting_period` (`relative`);

--
-- Constraints for table `core_accounting_entry_details`
--
ALTER TABLE `core_accounting_entry_details`
  ADD CONSTRAINT `core_accounting_entry_details_ibfk_1` FOREIGN KEY (`entry_relative`) REFERENCES `core_accounting_entry` (`relative`),
  ADD CONSTRAINT `core_accounting_entry_details_ibfk_2` FOREIGN KEY (`account_relative`) REFERENCES `core_accounting_plan` (`relative`),
  ADD CONSTRAINT `fk_entry_details_auxiliary` FOREIGN KEY (`auxiliary_id`) REFERENCES `core_accounting_auxiliary` (`relative`);

--
-- Constraints for table `core_accounting_plan`
--
ALTER TABLE `core_accounting_plan`
  ADD CONSTRAINT `core_accounting_plan_ibfk_1` FOREIGN KEY (`parent_relative`) REFERENCES `core_accounting_plan` (`relative`);