USE hm;

CREATE TABLE IF NOT EXISTS proveedores (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo VARCHAR(30) NOT NULL UNIQUE,
  nombre VARCHAR(180) NOT NULL,
  tipo_documento ENUM('RUC','Cédula','Pasaporte','Otro') NOT NULL DEFAULT 'RUC',
  documento VARCHAR(40) NULL UNIQUE,
  email VARCHAR(150) NULL, telefono VARCHAR(50) NULL,
  direccion VARCHAR(255) NULL, ciudad VARCHAR(100) NULL, contacto VARCHAR(150) NULL,
  banco VARCHAR(120) NULL, cuenta VARCHAR(80) NULL, alias_cuenta VARCHAR(120) NULL,
  estado ENUM('Activo','Inactivo') NOT NULL DEFAULT 'Activo',
  observaciones TEXT NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS obligaciones (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  proveedor_id INT UNSIGNED NOT NULL,
  fecha_obligacion DATE NOT NULL,
  fecha_vencimiento DATE NOT NULL,
  descripcion TEXT NOT NULL,
  monto DECIMAL(14,2) NOT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_obligaciones_proveedor FOREIGN KEY (proveedor_id) REFERENCES proveedores(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS obligacion_pagos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  obligacion_id INT UNSIGNED NOT NULL,
  fecha_pago DATE NOT NULL,
  importe DECIMAL(14,2) NOT NULL,
  observacion TEXT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_obligacion_pagos FOREIGN KEY (obligacion_id) REFERENCES obligaciones(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS obligacion_seguimientos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  obligacion_id INT UNSIGNED NOT NULL,
  detalle TEXT NOT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_obligacion_seguimientos FOREIGN KEY (obligacion_id) REFERENCES obligaciones(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS obligacion_documentos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  obligacion_id INT UNSIGNED NOT NULL,
  nombre_original VARCHAR(255) NOT NULL,
  archivo VARCHAR(255) NOT NULL,
  creado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_obligacion_documentos FOREIGN KEY (obligacion_id) REFERENCES obligaciones(id) ON DELETE CASCADE
) ENGINE=InnoDB;
