CREATE DATABASE IF NOT EXISTS estimaciones
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE estimaciones;

CREATE TABLE roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nombre VARCHAR(80) NOT NULL,
  clave VARCHAR(50) NOT NULL,
  descripcion VARCHAR(255) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_roles_clave (clave)
) ENGINE=InnoDB;

CREATE TABLE usuarios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rol_id BIGINT UNSIGNED NOT NULL,
  nombre VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  debe_cambiar_password TINYINT(1) NOT NULL DEFAULT 1,
  ultimo_acceso DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_usuarios_email (email),
  KEY idx_usuarios_rol (rol_id),
  CONSTRAINT fk_usuarios_rol FOREIGN KEY (rol_id) REFERENCES roles (id)
) ENGINE=InnoDB;

CREATE TABLE empresas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tipo ENUM('CONTRATISTA','SUPERVISION','DEPENDENCIA','OTRA') NOT NULL DEFAULT 'CONTRATISTA',
  razon_social VARCHAR(200) NOT NULL,
  nombre_comercial VARCHAR(200) NULL,
  rfc VARCHAR(13) NULL,
  representante_legal VARCHAR(150) NULL,
  telefono VARCHAR(30) NULL,
  fax VARCHAR(30) NULL,
  email VARCHAR(190) NULL,
  direccion TEXT NULL,
  colonia VARCHAR(150) NULL,
  ciudad VARCHAR(150) NULL,
  municipio VARCHAR(150) NULL,
  estado VARCHAR(100) NULL,
  codigo_postal VARCHAR(5) NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_empresas_rfc (rfc)
) ENGINE=InnoDB;

CREATE TABLE obras (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  clave VARCHAR(50) NOT NULL,
  codificacion_pgo VARCHAR(50) NULL,
  id_obra_externo VARCHAR(50) NULL,
  nombre VARCHAR(255) NOT NULL,
  descripcion TEXT NULL,
  ubicacion VARCHAR(255) NULL,
  region VARCHAR(150) NULL,
  distrito VARCHAR(150) NULL,
  municipio VARCHAR(150) NULL,
  localidad VARCHAR(150) NULL,
  estado VARCHAR(100) NULL,
  latitud DECIMAL(10,7) NULL,
  longitud DECIMAL(10,7) NULL,
  estatus ENUM('BORRADOR','ACTIVA','SUSPENDIDA','TERMINADA','CANCELADA') NOT NULL DEFAULT 'BORRADOR',
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_obras_clave (clave),
  KEY idx_obras_estatus (estatus),
  CONSTRAINT fk_obras_created_by FOREIGN KEY (created_by) REFERENCES usuarios (id)
) ENGINE=InnoDB;

CREATE TABLE contratos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  obra_id BIGINT UNSIGNED NOT NULL,
  contratista_id BIGINT UNSIGNED NOT NULL,
  numero VARCHAR(100) NOT NULL,
  id_pisoa VARCHAR(100) NOT NULL,
  objeto TEXT NULL,
  fecha_contrato DATE NOT NULL,
  fecha_inicio DATE NOT NULL,
  fecha_termino DATE NOT NULL,
  monto_sin_iva DECIMAL(18,2) NOT NULL DEFAULT 0,
  iva_porcentaje DECIMAL(6,3) NOT NULL DEFAULT 16.000,
  importe_iva DECIMAL(18,2) NOT NULL DEFAULT 0,
  importe_con_iva DECIMAL(18,2) NOT NULL DEFAULT 0,
  anticipo_porcentaje DECIMAL(6,3) NOT NULL DEFAULT 0,
  importe_anticipo DECIMAL(18,2) NOT NULL DEFAULT 0,
  fecha_anticipo DATE NULL,
  oficio_autorizacion VARCHAR(180) NULL,
  fecha_oficio_autorizacion DATE NULL,
  oficio_refrendo VARCHAR(180) NULL,
  fecha_oficio_refrendo DATE NULL,
  fianza_cumplimiento VARCHAR(100) NULL,
  fianza_anticipo VARCHAR(100) NULL,
  afianzadora VARCHAR(200) NULL,
  estatus ENUM('BORRADOR','VIGENTE','SUSPENDIDO','TERMINADO','RESCINDIDO') NOT NULL DEFAULT 'BORRADOR',
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_contratos_numero (numero),
  UNIQUE KEY uk_contratos_id_pisoa (id_pisoa),
  KEY idx_contratos_obra (obra_id),
  KEY idx_contratos_contratista (contratista_id),
  CONSTRAINT fk_contratos_obra FOREIGN KEY (obra_id) REFERENCES obras (id),
  CONSTRAINT fk_contratos_contratista FOREIGN KEY (contratista_id) REFERENCES empresas (id),
  CONSTRAINT chk_contratos_fechas CHECK (fecha_termino >= fecha_inicio)
) ENGINE=InnoDB;

CREATE TABLE unidades (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  clave VARCHAR(20) NOT NULL,
  nombre VARCHAR(80) NOT NULL,
  UNIQUE KEY uk_unidades_clave (clave)
) ENGINE=InnoDB;

CREATE TABLE partidas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id BIGINT UNSIGNED NOT NULL,
  clave VARCHAR(50) NOT NULL,
  nombre VARCHAR(200) NOT NULL,
  edificio ENUM('A','B','C','D','OBRA_EXTERIOR') NOT NULL DEFAULT 'A',
  orden SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_partidas_contrato_clave (contrato_id, clave),
  CONSTRAINT fk_partidas_contrato FOREIGN KEY (contrato_id) REFERENCES contratos (id)
) ENGINE=InnoDB;

CREATE TABLE conceptos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id BIGINT UNSIGNED NOT NULL,
  partida_id BIGINT UNSIGNED NULL,
  unidad_id SMALLINT UNSIGNED NOT NULL,
  clave VARCHAR(60) NOT NULL,
  descripcion TEXT NOT NULL,
  cantidad_contratada DECIMAL(18,6) NOT NULL DEFAULT 0,
  precio_unitario DECIMAL(18,6) NOT NULL DEFAULT 0,
  orden INT UNSIGNED NOT NULL DEFAULT 0,
  tipo ENUM('CONTRATO','EXTRAORDINARIO') NOT NULL DEFAULT 'CONTRATO',
  activo TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_conceptos_contrato_clave (contrato_id, clave),
  KEY idx_conceptos_partida (partida_id),
  KEY idx_conceptos_unidad (unidad_id),
  CONSTRAINT fk_conceptos_contrato FOREIGN KEY (contrato_id) REFERENCES contratos (id),
  CONSTRAINT fk_conceptos_partida FOREIGN KEY (partida_id) REFERENCES partidas (id),
  CONSTRAINT fk_conceptos_unidad FOREIGN KEY (unidad_id) REFERENCES unidades (id)
) ENGINE=InnoDB;

CREATE TABLE estimaciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id BIGINT UNSIGNED NOT NULL,
  numero SMALLINT UNSIGNED NOT NULL,
  periodo_inicio DATE NOT NULL,
  periodo_fin DATE NOT NULL,
  fecha_presentacion DATE NULL,
  fecha_recepcion DATE NULL,
  fecha_terminacion_real DATE NULL,
  dias_prorroga SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  dias_sancion SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  acta_observaciones TEXT NULL,
  tipo ENUM('ORDINARIA','EXTRAORDINARIA','FINIQUITO') NOT NULL DEFAULT 'ORDINARIA',
  estatus ENUM('BORRADOR','PRESENTADA','EN_REVISION','OBSERVADA','AUTORIZADA','PAGADA','CANCELADA') NOT NULL DEFAULT 'BORRADOR',
  subtotal DECIMAL(18,2) NOT NULL DEFAULT 0,
  iva DECIMAL(18,2) NOT NULL DEFAULT 0,
  deducciones DECIMAL(18,2) NOT NULL DEFAULT 0,
  neto DECIMAL(18,2) NOT NULL DEFAULT 0,
  observaciones TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_estimaciones_contrato_numero (contrato_id, numero),
  KEY idx_estimaciones_estatus (estatus),
  CONSTRAINT fk_estimaciones_contrato FOREIGN KEY (contrato_id) REFERENCES contratos (id),
  CONSTRAINT fk_estimaciones_created_by FOREIGN KEY (created_by) REFERENCES usuarios (id),
  CONSTRAINT chk_estimaciones_periodo CHECK (periodo_fin >= periodo_inicio)
) ENGINE=InnoDB;

CREATE TABLE estimacion_detalles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  estimacion_id BIGINT UNSIGNED NOT NULL,
  concepto_id BIGINT UNSIGNED NOT NULL,
  cantidad_periodo DECIMAL(18,6) NOT NULL DEFAULT 0,
  cantidad_ordinaria DECIMAL(18,6) NOT NULL DEFAULT 0,
  cantidad_excedente DECIMAL(18,6) NOT NULL DEFAULT 0,
  precio_unitario DECIMAL(18,6) NOT NULL DEFAULT 0,
  importe DECIMAL(18,2) NOT NULL DEFAULT 0,
  importe_excedente DECIMAL(18,2) NOT NULL DEFAULT 0,
  excedente_estatus VARCHAR(40) NOT NULL DEFAULT 'NO_APLICA',
  excedente_justificacion VARCHAR(1000) NULL,
  excedente_oficio VARCHAR(255) NULL,
  observaciones VARCHAR(500) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uk_detalles_estimacion_concepto (estimacion_id, concepto_id),
  KEY idx_detalles_concepto (concepto_id),
  CONSTRAINT fk_detalles_estimacion FOREIGN KEY (estimacion_id) REFERENCES estimaciones (id) ON DELETE CASCADE,
  CONSTRAINT fk_detalles_concepto FOREIGN KEY (concepto_id) REFERENCES conceptos (id)
) ENGINE=InnoDB;

CREATE TABLE generadores (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  estimacion_detalle_id BIGINT UNSIGNED NOT NULL,
  descripcion VARCHAR(255) NULL,
  operacion ENUM('SUMA','RESTA') NOT NULL DEFAULT 'SUMA',
  largo DECIMAL(18,6) NOT NULL DEFAULT 1,
  ancho DECIMAL(18,6) NOT NULL DEFAULT 1,
  alto DECIMAL(18,6) NOT NULL DEFAULT 1,
  piezas DECIMAL(18,6) NOT NULL DEFAULT 1,
  resultado DECIMAL(18,6) NOT NULL DEFAULT 0,
  ubicacion VARCHAR(255) NULL,
  referencia VARCHAR(150) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  KEY idx_generadores_detalle (estimacion_detalle_id),
  CONSTRAINT fk_generadores_detalle FOREIGN KEY (estimacion_detalle_id)
    REFERENCES estimacion_detalles (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE deduccion_tipos (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  clave VARCHAR(30) NOT NULL,
  nombre VARCHAR(120) NOT NULL,
  porcentaje_default DECIMAL(8,4) NULL,
  tipo_calculo ENUM('PORCENTAJE','MANUAL') NOT NULL DEFAULT 'PORCENTAJE',
  base_calculo ENUM('SUBTOTAL','TOTAL_CON_IVA','CONTRATO_SIN_IVA','MANUAL') NOT NULL DEFAULT 'SUBTOTAL',
  es_amortizacion TINYINT(1) NOT NULL DEFAULT 0,
  orden SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uk_deduccion_tipos_clave (clave)
) ENGINE=InnoDB;

CREATE TABLE estimacion_deducciones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  estimacion_id BIGINT UNSIGNED NOT NULL,
  deduccion_tipo_id SMALLINT UNSIGNED NOT NULL,
  base DECIMAL(18,2) NOT NULL DEFAULT 0,
  porcentaje DECIMAL(8,4) NOT NULL DEFAULT 0,
  importe DECIMAL(18,2) NOT NULL DEFAULT 0,
  observaciones VARCHAR(255) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  KEY idx_est_deducciones_estimacion (estimacion_id),
  UNIQUE KEY uk_estimacion_deduccion_tipo (estimacion_id, deduccion_tipo_id),
  CONSTRAINT fk_est_deducciones_estimacion FOREIGN KEY (estimacion_id)
    REFERENCES estimaciones (id) ON DELETE CASCADE,
  CONSTRAINT fk_est_deducciones_tipo FOREIGN KEY (deduccion_tipo_id)
    REFERENCES deduccion_tipos (id)
) ENGINE=InnoDB;

CREATE TABLE facturas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id BIGINT UNSIGNED NOT NULL,
  estimacion_id BIGINT UNSIGNED NULL,
  tipo ENUM('ANTICIPO','ESTIMACION','OTRA') NOT NULL DEFAULT 'ESTIMACION',
  uuid CHAR(36) NULL,
  serie VARCHAR(30) NULL,
  folio VARCHAR(50) NULL,
  fecha_emision DATETIME NULL,
  rfc_emisor VARCHAR(13) NULL,
  rfc_receptor VARCHAR(13) NULL,
  subtotal DECIMAL(18,2) NOT NULL DEFAULT 0,
  descuento DECIMAL(18,2) NOT NULL DEFAULT 0,
  iva DECIMAL(18,2) NOT NULL DEFAULT 0,
  total DECIMAL(18,2) NOT NULL DEFAULT 0,
  moneda VARCHAR(5) NOT NULL DEFAULT 'MXN',
  metodo_pago VARCHAR(10) NULL,
  forma_pago VARCHAR(10) NULL,
  uso_cfdi VARCHAR(10) NULL,
  estatus ENUM('BORRADOR','VIGENTE','CANCELADO') NOT NULL DEFAULT 'BORRADOR',
  xml_nombre VARCHAR(255) NULL,
  xml_ruta VARCHAR(500) NULL,
  xml_hash CHAR(64) NULL,
  pdf_nombre VARCHAR(255) NULL,
  pdf_ruta VARCHAR(500) NULL,
  observaciones VARCHAR(500) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_facturas_uuid (uuid),
  KEY idx_facturas_contrato (contrato_id),
  KEY idx_facturas_estimacion (estimacion_id),
  CONSTRAINT fk_facturas_contrato FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE CASCADE,
  CONSTRAINT fk_facturas_estimacion FOREIGN KEY (estimacion_id) REFERENCES estimaciones(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE cuenta_movimientos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id BIGINT UNSIGNED NOT NULL,
  estimacion_id BIGINT UNSIGNED NULL,
  factura_id BIGINT UNSIGNED NULL,
  tipo ENUM('ANTICIPO','ESTIMACION') NOT NULL,
  numero_documento VARCHAR(120) NULL,
  fecha_factura DATE NULL,
  importe_sin_iva DECIMAL(18,2) NOT NULL DEFAULT 0,
  iva DECIMAL(18,2) NOT NULL DEFAULT 0,
  importe_bruto DECIMAL(18,2) NOT NULL DEFAULT 0,
  deducciones DECIMAL(18,2) NOT NULL DEFAULT 0,
  neto DECIMAL(18,2) NOT NULL DEFAULT 0,
  fecha_tramite DATE NULL,
  fecha_pago DATE NULL,
  importe_pagado DECIMAL(18,2) NOT NULL DEFAULT 0,
  referencia_pago VARCHAR(150) NULL,
  estatus ENUM('PENDIENTE','EN_TRAMITE','PAGADO','CANCELADO') NOT NULL DEFAULT 'PENDIENTE',
  observaciones VARCHAR(500) NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_cuenta_estimacion (estimacion_id),
  KEY idx_cuenta_contrato (contrato_id),
  KEY idx_cuenta_factura (factura_id),
  CONSTRAINT fk_cuenta_contrato FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE CASCADE,
  CONSTRAINT fk_cuenta_estimacion FOREIGN KEY (estimacion_id) REFERENCES estimaciones(id) ON DELETE CASCADE,
  CONSTRAINT fk_cuenta_factura FOREIGN KEY (factura_id) REFERENCES facturas(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE documentos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  obra_id BIGINT UNSIGNED NULL,
  contrato_id BIGINT UNSIGNED NULL,
  estimacion_id BIGINT UNSIGNED NULL,
  estimacion_detalle_id BIGINT UNSIGNED NULL,
  tipo VARCHAR(50) NOT NULL,
  etapa ENUM('ANTES','DURANTE','DESPUES','NO_APLICA') NOT NULL DEFAULT 'NO_APLICA',
  titulo VARCHAR(200) NULL,
  descripcion TEXT NULL,
  fecha_evidencia DATE NULL,
  ubicacion_evidencia VARCHAR(255) NULL,
  orden SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  nombre_original VARCHAR(255) NOT NULL,
  ruta VARCHAR(500) NOT NULL,
  mime_type VARCHAR(100) NULL,
  tamano BIGINT UNSIGNED NULL,
  hash_sha256 CHAR(64) NULL,
  uploaded_by BIGINT UNSIGNED NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  KEY idx_documentos_obra (obra_id),
  KEY idx_documentos_contrato (contrato_id),
  KEY idx_documentos_estimacion (estimacion_id),
  KEY idx_documentos_estimacion_detalle (estimacion_detalle_id),
  CONSTRAINT fk_documentos_obra FOREIGN KEY (obra_id) REFERENCES obras (id),
  CONSTRAINT fk_documentos_contrato FOREIGN KEY (contrato_id) REFERENCES contratos (id),
  CONSTRAINT fk_documentos_estimacion FOREIGN KEY (estimacion_id) REFERENCES estimaciones (id),
  CONSTRAINT fk_documentos_estimacion_detalle FOREIGN KEY (estimacion_detalle_id) REFERENCES estimacion_detalles (id) ON DELETE SET NULL,
  CONSTRAINT fk_documentos_usuario FOREIGN KEY (uploaded_by) REFERENCES usuarios (id)
) ENGINE=InnoDB;

CREATE TABLE estimacion_historial (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  estimacion_id BIGINT UNSIGNED NOT NULL,
  usuario_id BIGINT UNSIGNED NULL,
  estatus_anterior VARCHAR(30) NULL,
  estatus_nuevo VARCHAR(30) NOT NULL,
  comentario TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_historial_estimacion (estimacion_id),
  CONSTRAINT fk_historial_estimacion FOREIGN KEY (estimacion_id)
    REFERENCES estimaciones (id) ON DELETE CASCADE,
  CONSTRAINT fk_historial_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios (id)
) ENGINE=InnoDB;

CREATE TABLE convenios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id BIGINT UNSIGNED NOT NULL,
  numero VARCHAR(100) NOT NULL,
  importe DECIMAL(18,2) NOT NULL DEFAULT 0,
  fecha DATE NULL,
  periodo_inicio DATE NULL,
  periodo_fin DATE NULL,
  observaciones TEXT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_convenios_contrato_numero (contrato_id, numero),
  CONSTRAINT fk_convenios_contrato FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE funcionarios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cargo VARCHAR(200) NOT NULL,
  nombre VARCHAR(200) NOT NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_funcionarios_cargo_nombre (cargo, nombre)
) ENGINE=InnoDB;

CREATE TABLE contrato_funcionarios (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id BIGINT UNSIGNED NOT NULL,
  funcionario_id BIGINT UNSIGNED NOT NULL,
  orden SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  UNIQUE KEY uk_contrato_funcionario (contrato_id, funcionario_id),
  CONSTRAINT fk_cf_contrato FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE CASCADE,
  CONSTRAINT fk_cf_funcionario FOREIGN KEY (funcionario_id) REFERENCES funcionarios(id)
) ENGINE=InnoDB;

CREATE TABLE fuentes_financiamiento (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  clave VARCHAR(50) NOT NULL,
  nombre VARCHAR(255) NOT NULL,
  programa VARCHAR(30) NULL,
  subprograma VARCHAR(30) NULL,
  numero_proyecto VARCHAR(50) NULL,
  numero_obra_sefin VARCHAR(50) NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_fuentes_clave (clave)
) ENGINE=InnoDB;

CREATE TABLE contrato_financiamientos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contrato_id BIGINT UNSIGNED NOT NULL,
  fuente_financiamiento_id BIGINT UNSIGNED NOT NULL,
  importe DECIMAL(18,2) NOT NULL DEFAULT 0,
  UNIQUE KEY uk_contrato_fuente (contrato_id, fuente_financiamiento_id),
  CONSTRAINT fk_cfin_contrato FOREIGN KEY (contrato_id) REFERENCES contratos(id) ON DELETE CASCADE,
  CONSTRAINT fk_cfin_fuente FOREIGN KEY (fuente_financiamiento_id) REFERENCES fuentes_financiamiento(id)
) ENGINE=InnoDB;

INSERT INTO deduccion_tipos (clave,nombre,porcentaje_default,tipo_calculo,base_calculo,es_amortizacion,orden,activo) VALUES
  ('AMORTIZACION','Amortización del anticipo',30.0000,'PORCENTAJE','TOTAL_CON_IVA',1,10,1),
  ('INSPECCION','5 al millar - Inspección y vigilancia',0.5000,'PORCENTAJE','SUBTOTAL',0,20,1),
  ('CAPACITACION','2 al millar - Fondo empresarial para capacitación',0.2000,'PORCENTAJE','SUBTOTAL',0,30,1),
  ('APOYO_SOCIAL','5 al millar - Apoyo social, estudios y proyectos',0.5000,'PORCENTAJE','SUBTOTAL',0,40,1),
  ('ISERTP','3% mano de obra (I.S.E.R.T.P.)',3.0000,'MANUAL','MANUAL',0,50,1),
  ('SUPERVISION','3% servicios de supervisión',3.0000,'PORCENTAJE','CONTRATO_SIN_IVA',0,60,1),
  ('SANCION','Sanción',NULL,'MANUAL','MANUAL',0,70,1),
  ('OTRA','Otra deducción',NULL,'MANUAL','MANUAL',0,80,1);

INSERT INTO roles (nombre, clave, descripcion, created_at)
VALUES
  ('Administrador', 'ADMIN', 'Acceso total al sistema', NOW()),
  ('Capturista', 'CAPTURISTA', 'Captura contratos y estimaciones', NOW()),
  ('Revisor', 'REVISOR', 'Revisa y observa estimaciones', NOW()),
  ('Autorizador', 'AUTORIZADOR', 'Autoriza estimaciones', NOW()),
  ('Supervisor', 'SUPERVISOR', 'Captura generadores y evidencia de obra', NOW()),
  ('Consulta', 'CONSULTA', 'Acceso de solo lectura', NOW());

INSERT INTO unidades (clave, nombre)
VALUES
  ('PZA', 'Pieza'),
  ('M', 'Metro'),
  ('M2', 'Metro cuadrado'),
  ('M3', 'Metro cúbico'),
  ('KG', 'Kilogramo'),
  ('TON', 'Tonelada'),
  ('LOTE', 'Lote');

INSERT INTO deduccion_tipos (clave, nombre, porcentaje_default)
VALUES
  ('AMORT_ANTICIPO', 'Amortización de anticipo', NULL),
  ('RETENCION', 'Retención', NULL),
  ('INSPECCION', 'Derecho de inspección', NULL),
  ('SANCION', 'Sanción', NULL),
  ('OTRA', 'Otra deducción', NULL);