-- ============================================================
-- SiteBuilder Admin — Database schema
-- MySQL 8 / MariaDB 10.5+
-- ============================================================

CREATE TABLE IF NOT EXISTS users (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  username    VARCHAR(64) UNIQUE NOT NULL,
  password    VARCHAR(255) NOT NULL,           -- bcrypt
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8mb4;

-- A "niche" is a category of website (Swimming, Yoga, Piano, etc.)
-- The data_fields column is JSON: an array of field definitions used as the
-- canonical schema for every business listing inside any website of this niche.
CREATE TABLE IF NOT EXISTS niches (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(120) NOT NULL,
  slug            VARCHAR(140) UNIQUE NOT NULL,
  description     TEXT,
  max_listings    INT DEFAULT 15,
  data_fields     JSON,                          -- [{key,label,type,required,options}, ...]
  theme_hints     TEXT,                          -- free text used by the site-generator to pick colours/icons
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS countries (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  code  CHAR(2) UNIQUE NOT NULL,                 -- ISO-3166-1 alpha-2
  name  VARCHAR(80) NOT NULL
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cities (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  country_id  INT NOT NULL,
  name        VARCHAR(120) NOT NULL,
  INDEX(country_id),
  FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS areas (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(120) NOT NULL,            -- "Edgware"
  postcode     VARCHAR(20),                       -- "HA8"
  country_id   INT NOT NULL,
  city_id      INT,
  slug         VARCHAR(140),
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (country_id) REFERENCES countries(id),
  FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL
) ENGINE=InnoDB CHARSET=utf8mb4;

-- A website is one (niche × area) pair.
CREATE TABLE IF NOT EXISTS websites (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(180) NOT NULL,         -- "Swimming Classes in Edgware"
  slug            VARCHAR(180) UNIQUE NOT NULL,  -- "swimming-classes-edgware"  (also folder name)
  domain          VARCHAR(180),
  niche_id        INT NOT NULL,
  area_id         INT NOT NULL,
  about_text      MEDIUMTEXT,                    -- editable about-page content
  status          ENUM('draft','listings_pending','listings_ready','site_built','published')
                  DEFAULT 'draft',
  theme_overrides JSON,                          -- per-site theme tweaks (colour, font, icon)
  meta_title      VARCHAR(255),
  meta_desc       VARCHAR(500),
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (niche_id) REFERENCES niches(id),
  FOREIGN KEY (area_id) REFERENCES areas(id)
) ENGINE=InnoDB CHARSET=utf8mb4;

-- Each listing's `data` JSON is the *content* and conforms to the niche.data_fields schema.
CREATE TABLE IF NOT EXISTS listings (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  website_id    INT NOT NULL,
  name          VARCHAR(255) NOT NULL,
  data          JSON,                              -- everything else (rating, postcode, summary, etc.)
  approved      TINYINT(1) DEFAULT 0,
  approved_at   DATETIME,
  rank_order    INT DEFAULT 999,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX(website_id),
  INDEX(approved),
  FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE
) ENGINE=InnoDB CHARSET=utf8mb4;

-- Long-form guide articles. Body is JSON so the renderer can keep structure
-- (sections, FAQs, headings) rather than a single blob.
CREATE TABLE IF NOT EXISTS guides (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  website_id   INT NOT NULL,
  title        VARCHAR(255) NOT NULL,
  slug         VARCHAR(255) NOT NULL,
  description  TEXT,
  body         JSON,                                -- { intro, sections:[{h,text}], faq:[{q,a}] }
  meta_title   VARCHAR(255),
  meta_desc    VARCHAR(500),
  status       ENUM('draft','published') DEFAULT 'draft',
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX(website_id),
  FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE
) ENGINE=InnoDB CHARSET=utf8mb4;

-- Universal text used by every generated site (terms, privacy, about template).
CREATE TABLE IF NOT EXISTS site_texts (
  `key`        VARCHAR(64) PRIMARY KEY,             -- 'terms', 'privacy', 'cookie_notice'
  body         MEDIUMTEXT,
  updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8mb4;

-- Batches: one row per outbound Claude batch job.
CREATE TABLE IF NOT EXISTS batches (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  website_id      INT,
  type            ENUM('fetch_listings','generate_about','generate_guide','build_site','generate_niche_fields')
                  NOT NULL,
  status          ENUM('queued','submitted','in_progress','ended','processed','failed','canceled')
                  DEFAULT 'queued',
  anthropic_id    VARCHAR(80),                       -- msgbatch_xxx
  request_payload JSON,                              -- the input we send (custom_id + params)
  result_payload  JSON,                              -- raw .jsonl results, parsed
  error_message   TEXT,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  submitted_at    DATETIME,
  completed_at    DATETIME,
  INDEX(status),
  INDEX(anthropic_id),
  FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE SET NULL
) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  `key`     VARCHAR(64) PRIMARY KEY,
  `value`   TEXT
) ENGINE=InnoDB CHARSET=utf8mb4;

-- Seed minimal data
INSERT IGNORE INTO countries (code, name) VALUES
  ('GB','United Kingdom'),
  ('US','United States'),
  ('CA','Canada'),
  ('AU','Australia'),
  ('IE','Ireland');

INSERT IGNORE INTO cities (country_id, name)
  SELECT id, 'London' FROM countries WHERE code='GB'
  UNION SELECT id, 'Manchester' FROM countries WHERE code='GB'
  UNION SELECT id, 'Birmingham' FROM countries WHERE code='GB'
  UNION SELECT id, 'New York' FROM countries WHERE code='US'
  UNION SELECT id, 'Los Angeles' FROM countries WHERE code='US'
  UNION SELECT id, 'Toronto' FROM countries WHERE code='CA'
  UNION SELECT id, 'Sydney' FROM countries WHERE code='AU'
  UNION SELECT id, 'Dublin' FROM countries WHERE code='IE';

INSERT IGNORE INTO site_texts (`key`, body) VALUES
  ('terms', '<h2>Terms of Service</h2><p>This site is an independent directory. We do not directly provide any services listed. All information is gathered from publicly available sources and may not be current. Verify details with each provider before booking.</p>'),
  ('privacy', '<h2>Privacy Policy</h2><p>We do not collect personal information from visitors. We use minimal cookies for site functionality. We do not sell or share data with third parties.</p>'),
  ('cookie_notice', 'This site uses essential cookies only. By continuing, you accept our use of cookies.');

-- Default admin user — username: admin / password: changeme
-- IMPORTANT: change this immediately after install.
INSERT IGNORE INTO users (username, password) VALUES
  ('admin', '$2y$10$9ko0nEvWH0MMDLdoTvgOvuuoUJnhSp2vlKkdF8vEEV2Mw196WvaAy');