-- ============================================================
-- SISTEMA CONTABLE CHILE - Schema Base de Datos
-- Compatible con SII Chile - PHP/MySQL
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE DATABASE IF NOT EXISTS `sistema_contable` 
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `sistema_contable`;

-- ============================================================
-- TABLA: empresas
-- ============================================================
CREATE TABLE IF NOT EXISTS `empresas` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `rut` VARCHAR(12) NOT NULL UNIQUE COMMENT 'RUT sin puntos con guion: 76543210-K',
  `razon_social` VARCHAR(200) NOT NULL,
  `nombre_fantasia` VARCHAR(200) DEFAULT NULL,
  `giro` VARCHAR(300) NOT NULL,
  `actividad_economica` VARCHAR(10) DEFAULT NULL,
  `domicilio` VARCHAR(300) DEFAULT NULL,
  `comuna` VARCHAR(100) DEFAULT NULL,
  `ciudad` VARCHAR(100) DEFAULT NULL,
  `telefono` VARCHAR(20) DEFAULT NULL,
  `email` VARCHAR(150) DEFAULT NULL,
  `regimen_tributario` ENUM('14A','14B','14D','microempresa','honorarios') NOT NULL DEFAULT '14A',
  `tipo_contribuyente` ENUM('empresa','persona_natural') DEFAULT 'empresa',
  `clave_sii` TEXT DEFAULT NULL COMMENT 'Clave SII cifrada AES-256',
  `token_sii` TEXT DEFAULT NULL COMMENT 'Token sesión SII vigente',
  `token_expira` DATETIME DEFAULT NULL,
  `plan_cuentas_base` ENUM('chile_pyme','chile_grande','personalizado') DEFAULT 'chile_pyme',
  `moneda` ENUM('CLP','USD','EUR','UF') DEFAULT 'CLP',
  `logo` VARCHAR(255) DEFAULT NULL,
  `activa` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_rut` (`rut`),
  INDEX `idx_activa` (`activa`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: usuarios
-- ============================================================
CREATE TABLE IF NOT EXISTS `usuarios` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nombre` VARCHAR(100) NOT NULL,
  `apellido` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `rol` ENUM('superadmin','admin','contador','solo_lectura') DEFAULT 'contador',
  `activo` TINYINT(1) DEFAULT 1,
  `ultimo_acceso` DATETIME DEFAULT NULL,
  `token_reset` VARCHAR(100) DEFAULT NULL,
  `token_reset_expira` DATETIME DEFAULT NULL,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_email` (`email`),
  INDEX `idx_activo` (`activo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: usuario_empresa (relación muchos-a-muchos)
-- ============================================================
CREATE TABLE IF NOT EXISTS `usuario_empresa` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `usuario_id` INT UNSIGNED NOT NULL,
  `empresa_id` INT UNSIGNED NOT NULL,
  `rol_empresa` ENUM('admin','contador','solo_lectura') DEFAULT 'contador',
  `permisos` JSON DEFAULT NULL COMMENT 'Permisos granulares por módulo',
  UNIQUE KEY `uk_user_empresa` (`usuario_id`, `empresa_id`),
  FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`empresa_id`) REFERENCES `empresas`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: plan_cuentas
-- ============================================================
CREATE TABLE IF NOT EXISTS `plan_cuentas` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `empresa_id` INT UNSIGNED NOT NULL,
  `codigo` VARCHAR(20) NOT NULL,
  `nombre` VARCHAR(200) NOT NULL,
  `tipo` ENUM('activo','pasivo','patrimonio','ingreso','gasto','resultado','orden') NOT NULL,
  `clasificacion` ENUM('circulante','no_circulante','corriente','no_corriente','operacional','no_operacional') DEFAULT NULL,
  `nivel` TINYINT UNSIGNED DEFAULT 1 COMMENT '1=mayor, 2=submarca, 3=auxiliar',
  `cuenta_padre_id` INT UNSIGNED DEFAULT NULL,
  `afecta_iva` TINYINT(1) DEFAULT 0,
  `afecta_renta` TINYINT(1) DEFAULT 1,
  `moneda_extranjera` TINYINT(1) DEFAULT 0,
  `activa` TINYINT(1) DEFAULT 1,
  `orden` INT DEFAULT 0,
  UNIQUE KEY `uk_empresa_codigo` (`empresa_id`, `codigo`),
  FOREIGN KEY (`empresa_id`) REFERENCES `empresas`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`cuenta_padre_id`) REFERENCES `plan_cuentas`(`id`) ON DELETE SET NULL,
  INDEX `idx_tipo` (`tipo`),
  INDEX `idx_nivel` (`nivel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: periodos
-- ============================================================
CREATE TABLE IF NOT EXISTS `periodos` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `empresa_id` INT UNSIGNED NOT NULL,
  `periodo` VARCHAR(7) NOT NULL COMMENT 'YYYY-MM',
  `anio` SMALLINT UNSIGNED NOT NULL,
  `mes` TINYINT UNSIGNED NOT NULL,
  `estado` ENUM('abierto','cerrado','bloqueado') DEFAULT 'abierto',
  `fecha_cierre` DATE DEFAULT NULL,
  `usuario_cierre_id` INT UNSIGNED DEFAULT NULL,
  UNIQUE KEY `uk_empresa_periodo` (`empresa_id`, `periodo`),
  FOREIGN KEY (`empresa_id`) REFERENCES `empresas`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: vouchers (Comprobantes contables / Libro Diario)
-- ============================================================
CREATE TABLE IF NOT EXISTS `vouchers` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `empresa_id` INT UNSIGNED NOT NULL,
  `numero` INT UNSIGNED NOT NULL,
  `fecha` DATE NOT NULL,
  `periodo` VARCHAR(7) NOT NULL,
  `tipo` ENUM('compra','venta','honorario','diario','egreso','ingreso','ajuste','apertura','cierre') NOT NULL,
  `glosa` TEXT NOT NULL,
  `total_debe` DECIMAL(14,2) DEFAULT 0.00,
  `total_haber` DECIMAL(14,2) DEFAULT 0.00,
  `diferencia` DECIMAL(14,2) GENERATED ALWAYS AS (`total_debe` - `total_haber`) STORED,
  `etiqueta` VARCHAR(100) DEFAULT NULL COMMENT 'Ej: excluir_base_imponible',
  `excluir_base_imponible` TINYINT(1) DEFAULT 0,
  `importado_sii` TINYINT(1) DEFAULT 0,
  `doc_sii_id` INT UNSIGNED DEFAULT NULL,
  `usuario_id` INT UNSIGNED DEFAULT NULL,
  `estado` ENUM('borrador','aprobado','anulado') DEFAULT 'aprobado',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_empresa_numero` (`empresa_id`, `numero`),
  FOREIGN KEY (`empresa_id`) REFERENCES `empresas`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL,
  INDEX `idx_periodo` (`periodo`),
  INDEX `idx_fecha` (`fecha`),
  INDEX `idx_tipo` (`tipo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: asientos (Detalle de vouchers)
-- ============================================================
CREATE TABLE IF NOT EXISTS `asientos` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `voucher_id` INT UNSIGNED NOT NULL,
  `empresa_id` INT UNSIGNED NOT NULL,
  `cuenta_id` INT UNSIGNED NOT NULL,
  `glosa` VARCHAR(300) DEFAULT NULL,
  `debe` DECIMAL(14,2) DEFAULT 0.00,
  `haber` DECIMAL(14,2) DEFAULT 0.00,
  `moneda_extranjera` DECIMAL(14,4) DEFAULT NULL,
  `tipo_cambio` DECIMAL(10,4) DEFAULT NULL,
  `orden` TINYINT UNSIGNED DEFAULT 1,
  FOREIGN KEY (`voucher_id`) REFERENCES `vouchers`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`empresa_id`) REFERENCES `empresas`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`cuenta_id`) REFERENCES `plan_cuentas`(`id`),
  INDEX `idx_voucher` (`voucher_id`),
  INDEX `idx_cuenta` (`cuenta_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: docs_sii (Documentos importados del SII)
-- ============================================================
CREATE TABLE IF NOT EXISTS `docs_sii` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `empresa_id` INT UNSIGNED NOT NULL,
  `tipo` ENUM('compra','venta','honorario') NOT NULL,
  `tipo_doc` SMALLINT UNSIGNED NOT NULL COMMENT '33=Factura,61=NC,56=ND,39=BolVta,52=Guia,110=ExportFact',
  `folio` VARCHAR(20) NOT NULL,
  `rut_contraparte` VARCHAR(12) NOT NULL,
  `razon_contraparte` VARCHAR(200) DEFAULT NULL,
  `fecha_emision` DATE NOT NULL,
  `fecha_recepcion` DATE DEFAULT NULL,
  `neto` DECIMAL(14,2) DEFAULT 0.00,
  `iva` DECIMAL(14,2) DEFAULT 0.00,
  `exento` DECIMAL(14,2) DEFAULT 0.00,
  `total` DECIMAL(14,2) NOT NULL,
  `monto_no_recuperable` DECIMAL(14,2) DEFAULT 0.00,
  `periodo` VARCHAR(7) NOT NULL,
  `contabilizado` TINYINT(1) DEFAULT 0,
  `voucher_id` INT UNSIGNED DEFAULT NULL,
  `estado_sii` ENUM('ACEPT','RECL','PEND','ANULADO') DEFAULT 'ACEPT',
  `xml_disponible` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_doc` (`empresa_id`, `tipo`, `tipo_doc`, `folio`, `rut_contraparte`),
  FOREIGN KEY (`empresa_id`) REFERENCES `empresas`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`voucher_id`) REFERENCES `vouchers`(`id`) ON DELETE SET NULL,
  INDEX `idx_periodo` (`periodo`),
  INDEX `idx_tipo` (`tipo`),
  INDEX `idx_contabilizado` (`contabilizado`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: honorarios
-- ============================================================
CREATE TABLE IF NOT EXISTS `honorarios` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `empresa_id` INT UNSIGNED NOT NULL,
  `doc_sii_id` INT UNSIGNED DEFAULT NULL,
  `rut_prestador` VARCHAR(12) NOT NULL,
  `nombre_prestador` VARCHAR(200) NOT NULL,
  `folio` VARCHAR(20) NOT NULL,
  `fecha` DATE NOT NULL,
  `periodo` VARCHAR(7) NOT NULL,
  `concepto` VARCHAR(300) DEFAULT NULL,
  `monto_bruto` DECIMAL(14,2) NOT NULL,
  `porcentaje_retencion` DECIMAL(5,2) DEFAULT 10.75,
  `retencion` DECIMAL(14,2) NOT NULL,
  `monto_liquido` DECIMAL(14,2) NOT NULL,
  `contabilizado` TINYINT(1) DEFAULT 0,
  `voucher_id` INT UNSIGNED DEFAULT NULL,
  FOREIGN KEY (`empresa_id`) REFERENCES `empresas`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`voucher_id`) REFERENCES `vouchers`(`id`) ON DELETE SET NULL,
  INDEX `idx_periodo` (`periodo`),
  INDEX `idx_rut_prestador` (`rut_prestador`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: caja_movimientos (Libro Caja / 14D)
-- ============================================================
CREATE TABLE IF NOT EXISTS `caja_movimientos` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `empresa_id` INT UNSIGNED NOT NULL,
  `voucher_id` INT UNSIGNED DEFAULT NULL,
  `fecha` DATE NOT NULL,
  `periodo` VARCHAR(7) NOT NULL,
  `tipo` ENUM('ingreso','egreso') NOT NULL,
  `concepto` VARCHAR(300) NOT NULL,
  `monto` DECIMAL(14,2) NOT NULL,
  `saldo` DECIMAL(14,2) DEFAULT NULL,
  `medio_pago` ENUM('efectivo','cheque','transferencia','tarjeta','otro') DEFAULT 'efectivo',
  `n_documento` VARCHAR(50) DEFAULT NULL,
  FOREIGN KEY (`empresa_id`) REFERENCES `empresas`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`voucher_id`) REFERENCES `vouchers`(`id`) ON DELETE SET NULL,
  INDEX `idx_periodo` (`periodo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: conciliacion_bancaria
-- ============================================================
CREATE TABLE IF NOT EXISTS `conciliacion_bancaria` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `empresa_id` INT UNSIGNED NOT NULL,
  `periodo` VARCHAR(7) NOT NULL,
  `cuenta_bancaria` VARCHAR(50) NOT NULL,
  `banco` VARCHAR(100) DEFAULT NULL,
  `saldo_banco` DECIMAL(14,2) DEFAULT NULL,
  `saldo_contable` DECIMAL(14,2) DEFAULT NULL,
  `diferencia` DECIMAL(14,2) GENERATED ALWAYS AS (`saldo_banco` - `saldo_contable`) STORED,
  `estado` ENUM('pendiente','conciliado') DEFAULT 'pendiente',
  `fecha_conciliacion` DATE DEFAULT NULL,
  FOREIGN KEY (`empresa_id`) REFERENCES `empresas`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: declaraciones_juradas
-- ============================================================
CREATE TABLE IF NOT EXISTS `declaraciones_juradas` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `empresa_id` INT UNSIGNED NOT NULL,
  `tipo` ENUM('DJ1879','DJ1887','DJ1835','DJ1945') NOT NULL,
  `periodo_tributario` VARCHAR(10) NOT NULL COMMENT 'YYYY o YYYY-MM',
  `estado` ENUM('generada','enviada','aceptada','rechazada') DEFAULT 'generada',
  `total_documentos` INT DEFAULT 0,
  `total_monto` DECIMAL(14,2) DEFAULT 0.00,
  `total_retencion` DECIMAL(14,2) DEFAULT 0.00,
  `folio_sii` VARCHAR(50) DEFAULT NULL,
  `fecha_envio` DATETIME DEFAULT NULL,
  `xml_generado` LONGTEXT DEFAULT NULL,
  `usuario_id` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`empresa_id`) REFERENCES `empresas`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`usuario_id`) REFERENCES `usuarios`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: formulario_f29
-- ============================================================
CREATE TABLE IF NOT EXISTS `formulario_f29` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `empresa_id` INT UNSIGNED NOT NULL,
  `periodo` VARCHAR(7) NOT NULL,
  `debitos_iva` DECIMAL(14,2) DEFAULT 0.00,
  `creditos_iva` DECIMAL(14,2) DEFAULT 0.00,
  `iva_determinado` DECIMAL(14,2) DEFAULT 0.00,
  `retencion_honorarios` DECIMAL(14,2) DEFAULT 0.00,
  `ppm` DECIMAL(14,2) DEFAULT 0.00,
  `total_impuestos` DECIMAL(14,2) DEFAULT 0.00,
  `estado` ENUM('borrador','declarado') DEFAULT 'borrador',
  `codigos_f29` JSON DEFAULT NULL COMMENT 'Todos los códigos del F29',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_empresa_periodo` (`empresa_id`, `periodo`),
  FOREIGN KEY (`empresa_id`) REFERENCES `empresas`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLA: auditoria_log
-- ============================================================
CREATE TABLE IF NOT EXISTS `auditoria_log` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `usuario_id` INT UNSIGNED DEFAULT NULL,
  `empresa_id` INT UNSIGNED DEFAULT NULL,
  `accion` VARCHAR(100) NOT NULL,
  `modulo` VARCHAR(50) NOT NULL,
  `registro_id` INT UNSIGNED DEFAULT NULL,
  `datos_antes` JSON DEFAULT NULL,
  `datos_despues` JSON DEFAULT NULL,
  `ip` VARCHAR(45) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_usuario` (`usuario_id`),
  INDEX `idx_empresa` (`empresa_id`),
  INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- DATOS INICIALES
-- ============================================================

-- Usuario administrador por defecto (password: Admin123!)
INSERT INTO `usuarios` (`nombre`, `apellido`, `email`, `password_hash`, `rol`) VALUES
('Administrador', 'Sistema', 'admin@sistema.cl', '$2y$12$5vHFMpOFjRZm1hpLJhJR8.Ob1nB7LVXyN2a1dP4mQKqq5WN.1qNZe', 'superadmin');

SET FOREIGN_KEY_CHECKS = 1;