20 lines
1.1 KiB
SQL
Executable File
20 lines
1.1 KiB
SQL
Executable File
-- Migración para relacionar gastos con conceptos globales
|
|
-- Permite que un gasto se asocie a uno o varios conceptos globales
|
|
-- Esto permite calcular el balance correcto por concepto: (Recaudado - Gastado)
|
|
|
|
-- Tabla de relación muchos-a-muchos: gastos <-> conceptos globales
|
|
CREATE TABLE IF NOT EXISTS `expense_concept_collections` (
|
|
`id` INT AUTO_INCREMENT PRIMARY KEY,
|
|
`expense_id` INT NOT NULL,
|
|
`concept_id` INT NOT NULL,
|
|
`amount` DECIMAL(10,2) NOT NULL COMMENT 'Monto del gasto asignado a este concepto',
|
|
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (`expense_id`) REFERENCES `expenses`(`id`) ON DELETE CASCADE,
|
|
FOREIGN KEY (`concept_id`) REFERENCES `finance_collection_concepts`(`id`) ON DELETE CASCADE,
|
|
UNIQUE KEY `unique_expense_concept` (`expense_id`, `concept_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Índice para optimizar consultas
|
|
CREATE INDEX idx_expense_concept_expense ON expense_concept_collections(expense_id);
|
|
CREATE INDEX idx_expense_concept_concept ON expense_concept_collections(concept_id);
|