-- sql/schema.sql
CREATE TABLE IF NOT EXISTS tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email_notify VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tenant_domains (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  domain VARCHAR(255) NOT NULL,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id),
  CONSTRAINT fk_td_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tenant_api_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  `key` VARCHAR(64) NOT NULL UNIQUE,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id),
  CONSTRAINT fk_tk_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tenant_settings (
  tenant_id INT PRIMARY KEY,
  greeting_text VARCHAR(500) DEFAULT '¡Hola! ¿En qué puedo ayudarte?',
  theme_color VARCHAR(7) DEFAULT '#2563eb',
  lang VARCHAR(5) DEFAULT 'es',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS faqs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  parent_id INT NULL,
  prompt_text VARCHAR(500) NOT NULL,
  reply_text TEXT NULL,
  requires_lead TINYINT(1) DEFAULT 0,
  sort_order INT DEFAULT 0,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id),
  INDEX (tenant_id, parent_id),
  CONSTRAINT fk_faq_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS conversations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  session_id VARCHAR(64) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_conv (tenant_id, session_id),
  INDEX (tenant_id, session_id),
  CONSTRAINT fk_conv_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  session_id VARCHAR(64) NOT NULL,
  type ENUM('text','option') NOT NULL,
  text VARCHAR(1000) NULL,
  option_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id, session_id, created_at),
  CONSTRAINT fk_msg_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leads (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  session_id VARCHAR(64) NOT NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(255) NOT NULL,
  message VARCHAR(1000) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX (tenant_id, created_at),
  CONSTRAINT fk_lead_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Datos de ejemplo
INSERT INTO tenants (id, name, email_notify) VALUES (1, 'Tenant Demo', 'demo@tu-dominio.com')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO tenant_domains (tenant_id, domain) VALUES (1, 'localhost'), (1, 'example.com')
ON DUPLICATE KEY UPDATE domain = VALUES(domain);

INSERT INTO tenant_api_keys (tenant_id, `key`, active) VALUES (1, 'DEMO_KEY_123', 1)
ON DUPLICATE KEY UPDATE `key` = VALUES(`key`);

INSERT INTO tenant_settings (tenant_id, greeting_text, theme_color, lang)
VALUES (1, '¡Hola! Soy el asistente virtual. ¿Qué tema te interesa?', '#2563eb', 'es')
ON DUPLICATE KEY UPDATE greeting_text = VALUES(greeting_text);

INSERT INTO faqs (tenant_id, parent_id, prompt_text, reply_text, requires_lead, sort_order) VALUES
(1, NULL, 'Planes y precios', 'Ofrecemos planes Básico, Pro y Empresa. ¿Quieres que un asesor te contacte?', 1, 1),
(1, NULL, 'Soporte técnico', 'Cuéntanos tu problema o elige una categoría.', 0, 2),
(1, NULL, 'Horario y contacto', 'Nuestro horario es L-V 9:00-18:00. ¿Deseas dejar tus datos?', 1, 3),
(1, 2, 'Problemas de acceso', 'Revisa tu correo y contraseña. Si persiste, deja tus datos y te llamamos.', 1, 1),
(1, 2, 'Facturación', 'Para temas de facturación, podemos contactarte. ¿Te parece?', 1, 2);