-- Phi Review: esquema completo (pegar en phpMyAdmin, base de datos vacía o nueva)
-- Todas las tablas cuelgan de tenants.id (un local = un cliente).

CREATE TABLE IF NOT EXISTS tenants (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(80) UNIQUE NOT NULL,
  name VARCHAR(150) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  business_type VARCHAR(30) NOT NULL DEFAULT 'general',
  logo_path VARCHAR(255) NULL,
  brand_color VARCHAR(7) NULL,
  brand_color_2 VARCHAR(7) NULL,
  admin_accent_color VARCHAR(7) NULL,
  google_review_url VARCHAR(500) NULL,
  google_min_stars TINYINT UNSIGNED NOT NULL DEFAULT 4,
  welcome_title VARCHAR(160) NULL,
  thanks_positive VARCHAR(255) NULL,
  thanks_negative VARCHAR(255) NULL,
  alert_email VARCHAR(160) NULL,
  qr_code VARCHAR(12) UNIQUE NOT NULL,
  qr_current_link_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT UNSIGNED NULL,               -- NULL = superadmin
  username VARCHAR(80) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reviews (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT UNSIGNED NOT NULL,
  stars TINYINT UNSIGNED NOT NULL,
  comment TEXT NULL,
  customer_name VARCHAR(120) NULL,
  customer_contact VARCHAR(160) NULL,
  to_google TINYINT(1) NOT NULL DEFAULT 0,
  google_clicked_at DATETIME NULL,
  status VARCHAR(12) NOT NULL DEFAULT 'new',   -- new | attended
  internal_note VARCHAR(500) NULL,
  attended_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_reviews_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_reviews_tenant_date (tenant_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Enlaces guardados a los que puede apuntar el QR fijo del local
CREATE TABLE IF NOT EXISTS qr_links (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT UNSIGNED NOT NULL,
  label VARCHAR(100) NOT NULL,
  url VARCHAR(500) NOT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_qrlinks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_qrlinks_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Historial de cambios de destino del QR
CREATE TABLE IF NOT EXISTS qr_log (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT UNSIGNED NOT NULL,
  label VARCHAR(100) NOT NULL,
  url VARCHAR(500) NULL,
  changed_by VARCHAR(80) NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_qrlog_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_qrlog_tenant (tenant_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Eventos del embudo: scan (QR/NFC), view (abrió la página), google_click
CREATE TABLE IF NOT EXISTS events (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT UNSIGNED NOT NULL,
  kind VARCHAR(20) NOT NULL,
  link_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_events_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  INDEX idx_events_tenant (tenant_id, kind, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Primer superadmin: genera el hash con admin/setup-hash.php (de un solo uso, bórralo después)
-- INSERT INTO admin_users (tenant_id, username, password_hash, created_at)
-- VALUES (NULL, 'juanfernando', 'PEGA_AQUI_EL_HASH', NOW());