

CREATE TABLE IF NOT EXISTS companies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  name_en VARCHAR(255) NULL,
  field VARCHAR(255) NULL,
  description MEDIUMTEXT NULL,
  phone VARCHAR(120) NULL,
  email VARCHAR(190) NULL,
  website VARCHAR(255) NULL,
  city VARCHAR(120) NULL,
  province VARCHAR(120) NULL,
  hall VARCHAR(50) NULL,
  row_name VARCHAR(50) NULL,
  booth VARCHAR(50) NULL,

  is_vip TINYINT(1) NOT NULL DEFAULT 0,
  vip_level INT NOT NULL DEFAULT 0,

  boost_enabled TINYINT(1) NOT NULL DEFAULT 0,
  boost_text VARCHAR(255) NULL,
  boost_start DATETIME NULL,
  boost_end DATETIME NULL,

  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE INDEX idx_companies_name ON companies(name);
CREATE INDEX idx_companies_field ON companies(field);
CREATE INDEX idx_companies_vip ON companies(is_vip, vip_level);
CREATE INDEX idx_companies_boost ON companies(boost_enabled);

CREATE TABLE IF NOT EXISTS tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS company_tags (
  company_id INT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY(company_id, tag_id),
  CONSTRAINT fk_ct_company FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_ct_tag FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS company_relations (
  company_id INT NOT NULL,
  related_company_id INT NOT NULL,
  weight INT NOT NULL DEFAULT 10,
  PRIMARY KEY(company_id, related_company_id),
  CONSTRAINT fk_cr_company FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_cr_related FOREIGN KEY(related_company_id) REFERENCES companies(id) ON DELETE CASCADE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS faq (
  id INT AUTO_INCREMENT PRIMARY KEY,
  question VARCHAR(255) NOT NULL,
  answer MEDIUMTEXT NOT NULL,
  keywords VARCHAR(255) NULL,
  priority INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE INDEX idx_faq_priority ON faq(priority, is_active);

CREATE TABLE IF NOT EXISTS site_data (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  url TEXT NOT NULL,
  url_hash CHAR(32) NOT NULL,
  title VARCHAR(255) NULL,
  content MEDIUMTEXT NULL,
  content_hash CHAR(32) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_url_hash (url_hash)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE INDEX idx_site_data_title ON site_data(title);

CREATE TABLE IF NOT EXISTS chat_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  question TEXT NOT NULL,
  intent VARCHAR(50) NOT NULL DEFAULT 'general',
  source VARCHAR(30) NULL,
  company_id INT NULL,
  ip VARCHAR(45) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE INDEX idx_logs_created_at ON chat_logs(created_at);
CREATE INDEX idx_logs_intent ON chat_logs(intent);
CREATE INDEX idx_logs_source ON chat_logs(source);

CREATE TABLE IF NOT EXISTS rate_limits (
  ip VARCHAR(45) PRIMARY KEY,
  hits INT NOT NULL DEFAULT 0,
  window_start TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
