-- Combined install SQL: Outscraper Business Discovery Engine + Outscraper URL SEO Importer
-- Compatible with older shared-hosting MySQL/MariaDB. No ADD COLUMN IF NOT EXISTS, no row_number column.
-- Default admin login for both admins: admin / admin12345

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  last_login_at DATETIME NULL,
  created_at DATETIME DEFAULT NULL,
  updated_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_admin_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO admins (username,password_hash,created_at,updated_at)
SELECT 'admin', '$2y$12$OBuJRm6FtWihLRnw5ExT3Oo2kdVnMygWEcc11FyJ3C0vyU7liVUcS', NOW(), NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM admins WHERE username='admin');

CREATE TABLE IF NOT EXISTS discovery_jobs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  location_town VARCHAR(190) NOT NULL,
  location_county VARCHAR(190) NOT NULL,
  requested_limit INT UNSIGNED NOT NULL DEFAULT 12,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  total_queries INT UNSIGNED NOT NULL DEFAULT 0,
  processed_queries INT UNSIGNED NOT NULL DEFAULT 0,
  inserted_count INT UNSIGNED NOT NULL DEFAULT 0,
  skipped_duplicates INT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL,
  completed_at DATETIME NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS discovery_job_queries (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  job_id INT UNSIGNED NOT NULL,
  category VARCHAR(190) NOT NULL,
  query_text VARCHAR(255) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  results_found INT UNSIGNED NOT NULL DEFAULT 0,
  inserted_count INT UNSIGNED NOT NULL DEFAULT 0,
  skipped_duplicates INT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL,
  completed_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_djq_job (job_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS businesses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  business_name VARCHAR(255) NOT NULL,
  category VARCHAR(190) NOT NULL,
  address TEXT NULL,
  phone VARCHAR(100) NULL,
  normalized_phone VARCHAR(100) NULL,
  website VARCHAR(500) NULL,
  website_domain VARCHAR(190) NULL,
  opening_hours LONGTEXT NULL,
  google_maps_url VARCHAR(700) NULL,
  rating DECIMAL(3,2) NULL,
  reviews_count INT NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  source_query VARCHAR(255) NULL,
  raw_json LONGTEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_business_category (category),
  KEY idx_business_domain (website_domain),
  KEY idx_business_phone (normalized_phone),
  KEY idx_business_name (business_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS exports (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  filename VARCHAR(255) NOT NULL,
  file_path VARCHAR(700) NOT NULL,
  total_rows INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS articles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  excerpt TEXT DEFAULT NULL,
  content_html LONGTEXT DEFAULT NULL,
  keywords TEXT DEFAULT NULL,
  source_url TEXT DEFAULT NULL,
  header_image_url TEXT DEFAULT NULL,
  status ENUM('draft','published','unpublished') NOT NULL DEFAULT 'draft',
  created_at DATETIME DEFAULT NULL,
  updated_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  KEY status (status),
  KEY created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS outscraper_import_batches (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  batch_name VARCHAR(255) NOT NULL,
  original_filename VARCHAR(255) DEFAULT NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'queued',
  total_rows INT UNSIGNED NOT NULL DEFAULT 0,
  inserted_rows INT UNSIGNED NOT NULL DEFAULT 0,
  duplicate_rows INT UNSIGNED NOT NULL DEFAULT 0,
  failed_rows INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT NULL,
  finished_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  KEY status (status),
  KEY created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS outscraper_businesses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  batch_id INT UNSIGNED DEFAULT NULL,
  import_row_number INT UNSIGNED DEFAULT NULL,
  business_name VARCHAR(255) NOT NULL,
  category VARCHAR(160) DEFAULT NULL,
  address TEXT DEFAULT NULL,
  phone VARCHAR(100) DEFAULT NULL,
  website TEXT DEFAULT NULL,
  opening_hours_json LONGTEXT DEFAULT NULL,
  google_maps_url LONGTEXT DEFAULT NULL,
  rating DECIMAL(3,2) DEFAULT NULL,
  reviews_count INT UNSIGNED DEFAULT NULL,
  latitude DECIMAL(12,8) DEFAULT NULL,
  longitude DECIMAL(12,8) DEFAULT NULL,
  source_query VARCHAR(255) DEFAULT NULL,
  website_domain VARCHAR(190) DEFAULT NULL,
  dedupe_key VARCHAR(80) NOT NULL,
  import_status VARCHAR(40) NOT NULL DEFAULT 'imported',
  enrichment_status VARCHAR(40) NOT NULL DEFAULT 'pending',
  image_status VARCHAR(40) NOT NULL DEFAULT 'pending',
  article_status VARCHAR(40) NOT NULL DEFAULT 'pending',
  about_url TEXT DEFAULT NULL,
  extracted_description LONGTEXT DEFAULT NULL,
  extracted_emails TEXT DEFAULT NULL,
  extracted_socials TEXT DEFAULT NULL,
  source_image_url TEXT DEFAULT NULL,
  image_provider VARCHAR(80) DEFAULT NULL,
  image_credit_name VARCHAR(255) DEFAULT NULL,
  image_credit_url TEXT DEFAULT NULL,
  local_image_path TEXT DEFAULT NULL,
  article_id INT UNSIGNED DEFAULT NULL,
  crawl_error TEXT DEFAULT NULL,
  last_crawled_at DATETIME DEFAULT NULL,
  created_at DATETIME DEFAULT NULL,
  updated_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY dedupe_key (dedupe_key),
  KEY batch_id (batch_id),
  KEY website_domain (website_domain),
  KEY enrichment_status (enrichment_status),
  KEY article_status (article_status),
  KEY image_status (image_status),
  KEY article_id (article_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;