-- ICYLAB OS v9.0 - Fase 3: Tesoreria y flujo de caja de 13 semanas
-- Ejecutar una sola vez desde phpMyAdmin sobre la base de datos de ICYLAB OS.
-- El flujo de caja usa importes BRUTOS porque representa dinero real en caja/banco.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS treasury_opening_balances (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  cash_account_id INT UNSIGNED NOT NULL,
  balance_date DATE NOT NULL,
  amount DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  currency_code VARCHAR(10) NOT NULL DEFAULT 'CLP',
  is_reconciled TINYINT(1) NOT NULL DEFAULT 0,
  notes VARCHAR(1000) 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_treasury_opening_account_date (cash_account_id,balance_date),
  KEY idx_treasury_opening_date (balance_date,is_reconciled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS treasury_manual_movements (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  cash_account_id INT UNSIGNED NOT NULL,
  movement_date DATE NOT NULL,
  value_date DATE DEFAULT NULL,
  direction ENUM('inflow','outflow') NOT NULL,
  cash_activity ENUM('operations','investment','financing','internal_transfer') NOT NULL DEFAULT 'operations',
  amount DECIMAL(16,2) NOT NULL,
  description VARCHAR(255) NOT NULL,
  counterparty VARCHAR(190) DEFAULT NULL,
  external_reference VARCHAR(120) DEFAULT NULL,
  finance_center_id INT UNSIGNED DEFAULT NULL,
  status ENUM('draft','confirmed','void') NOT NULL DEFAULT 'confirmed',
  transfer_group VARCHAR(64) DEFAULT NULL,
  notes VARCHAR(1000) DEFAULT NULL,
  confirmed_by INT DEFAULT NULL,
  confirmed_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),
  KEY idx_treasury_manual_account_date (cash_account_id,movement_date,status),
  KEY idx_treasury_manual_transfer (transfer_group),
  KEY idx_treasury_manual_center (finance_center_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS treasury_statement_imports (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  cash_account_id INT UNSIGNED NOT NULL,
  original_filename VARCHAR(255) NOT NULL,
  file_hash CHAR(64) NOT NULL,
  statement_from DATE DEFAULT NULL,
  statement_to DATE DEFAULT NULL,
  rows_total INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('imported','reconciled','error') NOT NULL DEFAULT 'imported',
  error_message VARCHAR(1000) 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_treasury_statement_file (cash_account_id,file_hash),
  KEY idx_treasury_statement_period (cash_account_id,statement_from,statement_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS treasury_statement_rows (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  import_id BIGINT UNSIGNED NOT NULL,
  cash_account_id INT UNSIGNED NOT NULL,
  row_date DATE NOT NULL,
  description VARCHAR(500) NOT NULL,
  reference VARCHAR(190) DEFAULT NULL,
  debit_amount DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  credit_amount DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  signed_amount DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  balance_after DECIMAL(16,2) DEFAULT NULL,
  row_hash CHAR(64) NOT NULL,
  reconciliation_status ENUM('pending','matched','converted','ignored') NOT NULL DEFAULT 'pending',
  matched_payment_id BIGINT UNSIGNED DEFAULT NULL,
  matched_manual_movement_id BIGINT UNSIGNED DEFAULT NULL,
  matched_at DATETIME DEFAULT NULL,
  matched_by INT DEFAULT NULL,
  notes VARCHAR(1000) 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_treasury_statement_row (cash_account_id,row_hash),
  KEY idx_treasury_statement_pending (cash_account_id,reconciliation_status,row_date),
  KEY idx_treasury_statement_import (import_id),
  CONSTRAINT fk_treasury_statement_import FOREIGN KEY (import_id) REFERENCES treasury_statement_imports (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS treasury_scenarios (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  scenario_key VARCHAR(30) NOT NULL,
  scenario_name VARCHAR(100) NOT NULL,
  inflow_factor DECIMAL(7,4) NOT NULL DEFAULT 1.0000,
  outflow_factor DECIMAL(7,4) NOT NULL DEFAULT 1.0000,
  is_system TINYINT(1) NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  description VARCHAR(500) 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_treasury_scenario_key (scenario_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS treasury_scenario_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scenario_id INT UNSIGNED NOT NULL,
  item_type ENUM('inflow','outflow') NOT NULL,
  expected_date DATE NOT NULL,
  amount DECIMAL(16,2) NOT NULL,
  probability_pct DECIMAL(5,2) NOT NULL DEFAULT 100.00,
  description VARCHAR(255) NOT NULL,
  counterparty VARCHAR(190) DEFAULT NULL,
  cash_activity ENUM('operations','investment','financing') NOT NULL DEFAULT 'operations',
  finance_center_id INT UNSIGNED DEFAULT NULL,
  status ENUM('active','realized','cancelled') NOT NULL DEFAULT 'active',
  notes VARCHAR(1000) 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_treasury_scenario_date (scenario_id,status,expected_date),
  KEY idx_treasury_scenario_center (finance_center_id),
  CONSTRAINT fk_treasury_scenario_item FOREIGN KEY (scenario_id) REFERENCES treasury_scenarios (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS treasury_forecast_coverage (
  id TINYINT UNSIGNED NOT NULL,
  payroll_complete TINYINT(1) NOT NULL DEFAULT 0,
  taxes_complete TINYINT(1) NOT NULL DEFAULT 0,
  financing_complete TINYINT(1) NOT NULL DEFAULT 0,
  other_expenses_complete TINYINT(1) NOT NULL DEFAULT 0,
  confirmed_through DATE DEFAULT NULL,
  notes VARCHAR(1000) DEFAULT NULL,
  confirmed_by INT DEFAULT NULL,
  confirmed_at DATETIME DEFAULT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO treasury_forecast_coverage
  (id,payroll_complete,taxes_complete,financing_complete,other_expenses_complete)
VALUES (1,0,0,0,0)
ON DUPLICATE KEY UPDATE id=VALUES(id);

CREATE TABLE IF NOT EXISTS treasury_audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  entity_type VARCHAR(50) NOT NULL,
  entity_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_treasury_audit_entity (entity_type,entity_id),
  KEY idx_treasury_audit_date (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO treasury_scenarios
  (scenario_key,scenario_name,inflow_factor,outflow_factor,is_system,is_active,description)
VALUES
  ('conservative','Conservador',0.7500,1.0500,1,1,'Reduce entradas tentativas y tensiona salidas para anticipar riesgo.'),
  ('base','Base',1.0000,1.0000,1,1,'Compromisos registrados y partidas de escenario ponderadas por probabilidad.'),
  ('optimistic','Optimista',1.1000,0.9500,1,1,'Sensibiliza partidas tentativas favorables sin alterar movimientos realizados.')
ON DUPLICATE KEY UPDATE
  scenario_name=VALUES(scenario_name),inflow_factor=VALUES(inflow_factor),outflow_factor=VALUES(outflow_factor),is_active=1;

INSERT INTO kpi_definition
  (kpi_id,name,definition_version,unit,favorable_direction,owner_role,formula_text,is_active)
VALUES
  ('FIN-CASH','Caja y runway','2.0','months','higher','CEO/CFO','saldo de caja bruto al inicio / promedio de salidas brutas proyectadas de 13 semanas',1),
  ('FIN-CASH-BAL','Caja disponible','1.0','clp','higher','CEO/CFO','saldos iniciales por cuenta mas cobros confirmados menos pagos confirmados y movimientos manuales',1),
  ('FIN-CASH-MIN','Saldo minimo proyectado','1.0','clp','higher','CEO/CFO','menor saldo final semanal del forecast bruto de 13 semanas',1),
  ('FIN-CASH-DEFICIT','Semanas con deficit','1.0','int','lower','CEO/CFO','cantidad de semanas del forecast cuyo saldo final es menor a cero',1)
ON DUPLICATE KEY UPDATE
  name=VALUES(name),definition_version=VALUES(definition_version),unit=VALUES(unit),favorable_direction=VALUES(favorable_direction),owner_role=VALUES(owner_role),formula_text=VALUES(formula_text),is_active=1;

SELECT 'ICYLAB_OS_TREASURY_V9_0_OK' AS migration_status,
       (SELECT COUNT(*) FROM treasury_scenarios WHERE is_active=1) AS scenarios_count,
       (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name LIKE 'treasury_%') AS treasury_tables_count;
