-- Esquema de tablas (ejecútalo dentro de TU base de datos seleccionada)

CREATE TABLE tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  nif  VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','client') NOT NULL DEFAULT 'client',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE sii_jobs (
  id VARCHAR(32) PRIMARY KEY,
  tenant_id INT NOT NULL,
  user_id INT NOT NULL,
  status ENUM('running','ok','error') NOT NULL DEFAULT 'running',
  schema_name VARCHAR(190) NOT NULL,
  request_xml_path TEXT NULL,
  payload_xml_path TEXT NULL,
  log_path TEXT NULL,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_jobs_tenant_created (tenant_id, created_at)
) ENGINE=InnoDB;

-- Seed: empresa demo (puedes borrar luego)
INSERT INTO tenants (id,name,nif,created_at) VALUES (1,'Empresa Demo S.L.','A00000000',NOW());
