-- ICYLAB OS v8.8 - Fase 1: Costos de gestión
-- Ejecutar una sola vez desde phpMyAdmin sobre la base de datos de ICYLAB OS.
-- Los importes de gestión usan NETO cuando el IVA es recuperable.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS cost_categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_code VARCHAR(30) NOT NULL,
  category_name VARCHAR(150) NOT NULL,
  nature ENUM('fixed','variable','mixed') NOT NULL DEFAULT 'fixed',
  category_group ENUM('materials','labor','services','administration','sales','marketing','taxes','capex','other') NOT NULL DEFAULT 'other',
  default_iva_treatment ENUM('recoverable','nonrecoverable','no_iva') NOT NULL DEFAULT 'recoverable',
  cash_activity ENUM('operations','investment','financing') NOT NULL DEFAULT 'operations',
  default_finance_center_id INT UNSIGNED DEFAULT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  description TEXT DEFAULT NULL,
  created_by INT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cost_category_code (category_code),
  KEY idx_cost_category_active (is_active, category_group),
  KEY idx_cost_category_center (default_finance_center_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cost_recurring_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  rule_name VARCHAR(180) NOT NULL,
  category_id INT UNSIGNED NOT NULL,
  description VARCHAR(255) NOT NULL,
  supplier_id INT DEFAULT NULL,
  supplier_name VARCHAR(255) DEFAULT NULL,
  finance_center_id INT UNSIGNED DEFAULT NULL,
  iva_treatment ENUM('recoverable','nonrecoverable','no_iva') NOT NULL DEFAULT 'recoverable',
  net_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  tax_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  gross_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  management_cost_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  frequency ENUM('monthly','quarterly','semiannual','annual') NOT NULL DEFAULT 'monthly',
  day_of_month TINYINT UNSIGNED NOT NULL DEFAULT 1,
  start_period CHAR(7) NOT NULL,
  end_period CHAR(7) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT DEFAULT NULL,
  created_by INT DEFAULT NULL,
  updated_by INT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cost_rule_period (is_active, start_period, end_period),
  KEY idx_cost_rule_category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cost_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period CHAR(7) NOT NULL,
  entry_date DATE NOT NULL,
  due_date DATE DEFAULT NULL,
  payment_date DATE DEFAULT NULL,
  source_type ENUM('manual','dte','recurring','payroll') NOT NULL DEFAULT 'manual',
  source_reference VARCHAR(190) DEFAULT NULL,
  category_id INT UNSIGNED DEFAULT NULL,
  nature ENUM('fixed','variable','mixed') NOT NULL DEFAULT 'fixed',
  description VARCHAR(255) NOT NULL,
  supplier_id INT DEFAULT NULL,
  supplier_name VARCHAR(255) DEFAULT NULL,
  finance_center_id INT UNSIGNED DEFAULT NULL,
  iva_treatment ENUM('recoverable','nonrecoverable','no_iva') NOT NULL DEFAULT 'recoverable',
  net_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  tax_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  gross_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  management_cost_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  status ENUM('draft','approved','paid','void') NOT NULL DEFAULT 'draft',
  linked_inbox_document_id BIGINT UNSIGNED DEFAULT NULL,
  linked_collection_id BIGINT UNSIGNED DEFAULT NULL,
  recurring_rule_id BIGINT UNSIGNED DEFAULT NULL,
  payroll_entry_id BIGINT UNSIGNED DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  approved_by INT DEFAULT NULL,
  approved_at DATETIME DEFAULT NULL,
  created_by INT DEFAULT NULL,
  updated_by INT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cost_source_reference (source_type, source_reference),
  UNIQUE KEY uq_cost_inbox_document (linked_inbox_document_id),
  UNIQUE KEY uq_cost_collection (linked_collection_id),
  UNIQUE KEY uq_cost_recurring_period (recurring_rule_id, period),
  UNIQUE KEY uq_cost_payroll_entry (payroll_entry_id),
  KEY idx_cost_entries_period (period, status),
  KEY idx_cost_entries_category (category_id),
  KEY idx_cost_entries_center (finance_center_id),
  KEY idx_cost_entries_supplier (supplier_id),
  KEY idx_cost_entries_date (entry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cost_allocations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  cost_entry_id BIGINT UNSIGNED NOT NULL,
  finance_center_id INT UNSIGNED NOT NULL,
  allocation_percent DECIMAL(7,4) NOT NULL,
  allocated_management_amount DECIMAL(14,2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cost_allocation_entry_center (cost_entry_id, finance_center_id),
  KEY idx_cost_allocation_center (finance_center_id),
  CONSTRAINT fk_cost_allocation_entry FOREIGN KEY (cost_entry_id) REFERENCES cost_entries (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cost_payroll_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period CHAR(7) NOT NULL,
  person_code VARCHAR(80) NOT NULL,
  person_name VARCHAR(180) NOT NULL,
  role_name VARCHAR(150) DEFAULT NULL,
  employment_type ENUM('employee','fees','other') NOT NULL DEFAULT 'employee',
  base_salary DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  allowances DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  bonuses DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  employer_charges DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  fees DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  other_costs DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_company_cost DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  finance_center_id INT UNSIGNED DEFAULT NULL,
  expected_payment_date DATE DEFAULT NULL,
  effective_payment_date DATE DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  created_by INT DEFAULT NULL,
  updated_by INT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cost_payroll_person_period (period, person_code),
  KEY idx_cost_payroll_period (period),
  KEY idx_cost_payroll_center (finance_center_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cost_monthly_closes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  period CHAR(7) NOT NULL,
  close_status ENUM('open','closed') NOT NULL DEFAULT 'open',
  management_cost_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  net_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  tax_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  gross_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  entries_count INT UNSIGNED NOT NULL DEFAULT 0,
  snapshot_json LONGTEXT DEFAULT NULL,
  notes TEXT DEFAULT NULL,
  closed_by INT DEFAULT NULL,
  closed_at DATETIME DEFAULT NULL,
  reopened_by INT DEFAULT NULL,
  reopened_at DATETIME DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cost_monthly_close_period (period)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cost_audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity_type VARCHAR(40) NOT NULL,
  entity_id BIGINT UNSIGNED DEFAULT NULL,
  action_key VARCHAR(60) NOT NULL,
  period CHAR(7) DEFAULT NULL,
  before_json LONGTEXT DEFAULT NULL,
  after_json LONGTEXT DEFAULT NULL,
  actor_user_id INT DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cost_audit_entity (entity_type, entity_id),
  KEY idx_cost_audit_period (period, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cost_categories
  (category_code, category_name, nature, category_group, default_iva_treatment, cash_activity, is_system, is_active, description)
VALUES
  ('MAT','Materias primas e insumos','variable','materials','recoverable','operations',1,1,'Compras directamente asociadas a productos o producción.'),
  ('MOD','Mano de obra directa','variable','labor','no_iva','operations',1,1,'Costo laboral directamente asignable a producción.'),
  ('REM','Remuneraciones y costo empresa','fixed','labor','no_iva','operations',1,1,'Sueldos, bonos, asignaciones y cargas del empleador.'),
  ('ARR','Arriendo','fixed','administration','recoverable','operations',1,1,'Arriendos de instalaciones, bodegas u oficinas.'),
  ('SER','Servicios externos','fixed','services','recoverable','operations',1,1,'Servicios profesionales y operacionales contratados.'),
  ('CON','Consumos básicos','mixed','services','recoverable','operations',1,1,'Electricidad, agua, gas, telecomunicaciones y consumos similares.'),
  ('ADM','Administración general','fixed','administration','recoverable','operations',1,1,'Gastos administrativos no clasificados en otra categoría.'),
  ('VEN','Costos de venta','variable','sales','recoverable','operations',1,1,'Comisiones, despacho y otros costos variables de venta.'),
  ('MKT','Marketing','variable','marketing','recoverable','operations',1,1,'Campañas, publicidad y acciones comerciales.'),
  ('IMP','Impuestos y tasas','fixed','taxes','no_iva','operations',1,1,'Impuestos, patentes y tasas no recuperables.'),
  ('CAP','Inversiones y activos','fixed','capex','recoverable','investment',1,1,'Adquisiciones de activos e inversiones; separadas de operación.'),
  ('OTR','Otros costos','mixed','other','recoverable','operations',1,1,'Clasificación transitoria para costos no homologados.')
ON DUPLICATE KEY UPDATE
  category_name=VALUES(category_name), nature=VALUES(nature), category_group=VALUES(category_group),
  default_iva_treatment=VALUES(default_iva_treatment), cash_activity=VALUES(cash_activity), is_system=1;

-- Verificación rápida esperada: siete tablas y doce categorías base.
SELECT 'ICYLAB_OS_COSTS_V8_8_OK' AS migration_status,
       (SELECT COUNT(*) FROM cost_categories) AS categories_count;
