-- ICYLAB OS v8.9 · Fase 2
-- Costeo estándar versionado, componentes y fotografía inmutable del costo vendido.
-- Ejecutar una sola vez desde phpMyAdmin después de instalar v8.8.

SET NAMES utf8mb4;
SET time_zone = '-04:00';

CREATE TABLE IF NOT EXISTS product_cost_versions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id INT NOT NULL,
  version_number INT UNSIGNED NOT NULL,
  effective_from DATE NOT NULL,
  effective_to DATE DEFAULT NULL,
  status ENUM('draft','approved','retired') NOT NULL DEFAULT 'draft',
  costing_method ENUM('standard','manual','legacy') NOT NULL DEFAULT 'standard',
  output_quantity DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
  output_unit VARCHAR(50) NOT NULL DEFAULT 'unidad',
  yield_percent DECIMAL(7,4) NOT NULL DEFAULT 100.0000,
  material_cost DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  labor_cost DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  variable_overhead_cost DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  fixed_overhead_cost DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  other_cost DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_batch_cost DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  unit_standard_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  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_product_cost_version (product_id, version_number),
  KEY idx_product_cost_active (product_id, status, effective_from, effective_to),
  KEY idx_product_cost_effective (effective_from, effective_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_cost_components (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  cost_version_id BIGINT UNSIGNED NOT NULL,
  component_type ENUM('material','labor','service','variable_overhead','fixed_overhead','other') NOT NULL,
  component_product_id INT DEFAULT NULL,
  component_code VARCHAR(100) DEFAULT NULL,
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(14,4) NOT NULL DEFAULT 1.0000,
  unit_measure VARCHAR(50) NOT NULL DEFAULT 'unidad',
  unit_cost DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  waste_percent DECIMAL(7,4) NOT NULL DEFAULT 0.0000,
  total_component_cost DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  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_product_cost_component_version (cost_version_id, sort_order),
  KEY idx_product_cost_component_product (component_product_id),
  CONSTRAINT fk_product_cost_component_version FOREIGN KEY (cost_version_id) REFERENCES product_cost_versions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS billing_line_cost_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  billing_document_line_id BIGINT UNSIGNED NOT NULL,
  environment ENUM('sandbox','production') NOT NULL,
  facto_document_id VARCHAR(100) NOT NULL,
  document_date DATE NOT NULL,
  document_type_taxbureau VARCHAR(20) DEFAULT NULL,
  customer_id INT DEFAULT NULL,
  customer_name VARCHAR(255) DEFAULT NULL,
  currency_code VARCHAR(10) NOT NULL DEFAULT 'CLP',
  exchange_rate_value DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
  product_id INT DEFAULT NULL,
  sku VARCHAR(100) DEFAULT NULL,
  product_name VARCHAR(255) DEFAULT NULL,
  cost_version_id BIGINT UNSIGNED DEFAULT NULL,
  quantity DECIMAL(14,4) NOT NULL DEFAULT 0.0000,
  unit_standard_cost DECIMAL(14,4) DEFAULT NULL,
  total_standard_cost DECIMAL(14,2) DEFAULT NULL,
  net_revenue DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  contribution_margin DECIMAL(14,2) DEFAULT NULL,
  margin_percent DECIMAL(9,4) DEFAULT NULL,
  snapshot_source ENUM('approved_version','legacy_version','legacy_product_cost','missing_product','missing_cost') NOT NULL,
  cost_quality ENUM('validated','provisional','missing') NOT NULL DEFAULT 'missing',
  snapshot_json LONGTEXT 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_billing_line_cost_snapshot (billing_document_line_id),
  KEY idx_billing_cost_document (environment, facto_document_id),
  KEY idx_billing_cost_date (environment, document_date),
  KEY idx_billing_cost_product (product_id, document_date),
  KEY idx_billing_cost_customer (customer_id, document_date),
  KEY idx_billing_cost_quality (environment, cost_quality, document_date),
  CONSTRAINT fk_billing_cost_line FOREIGN KEY (billing_document_line_id) REFERENCES billing_document_lines (id) ON DELETE CASCADE,
  CONSTRAINT fk_billing_cost_version FOREIGN KEY (cost_version_id) REFERENCES product_cost_versions (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_cost_audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id INT DEFAULT NULL,
  cost_version_id BIGINT UNSIGNED DEFAULT NULL,
  action_key VARCHAR(60) NOT 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_product_cost_audit_product (product_id, created_at),
  KEY idx_product_cost_audit_version (cost_version_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inicialización controlada: conserva el costo actual del catálogo como versión
-- provisional. No se presenta como receta validada y puede reemplazarse con una
-- nueva versión estándar aprobada desde la interfaz.
INSERT INTO product_cost_versions
  (product_id, version_number, effective_from, status, costing_method, output_quantity,
   output_unit, yield_percent, total_batch_cost, unit_standard_cost, notes, created_at, updated_at)
SELECT p.id, 1, '2025-01-01', 'approved', 'legacy', 1.0000,
       COALESCE(NULLIF(p.unit,''),'unidad'), 100.0000, p.cost, p.cost,
       'Inicializado desde products.cost por ICYLAB OS v8.9. Requiere revisión y componentes.', NOW(), NOW()
FROM products p
WHERE p.cost > 0
  AND NOT EXISTS (SELECT 1 FROM product_cost_versions v WHERE v.product_id=p.id);

UPDATE kpi_definition
SET definition_version='2.0',
    formula_text='(venta neta CLP - costo estándar congelado por línea) / venta neta CLP * 100; publicar con cobertura documental >=95% y cobertura de costo >=90%',
    updated_at=NOW()
WHERE kpi_id='FIN-01.2';

SELECT 'ICYLAB_OS_PRODUCT_COSTING_V8_9_OK' AS migration_status,
       (SELECT COUNT(*) FROM product_cost_versions) AS versions_count,
       (SELECT COUNT(*) FROM product_cost_versions WHERE costing_method='legacy') AS provisional_legacy_count;
