CREATE TABLE sites (
  site_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  base_url TEXT NOT NULL UNIQUE,
  sitemap_url TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE crawl_runs (
  run_id INTEGER PRIMARY KEY AUTOINCREMENT,
  site_id INTEGER NOT NULL,
  started_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at TEXT NULL,
  status TEXT NOT NULL DEFAULT 'running' CHECK (status IN ('running','finished','failed')),
  notes TEXT NULL,
  urls_from_sitemap INTEGER NOT NULL DEFAULT 0,
  urls_crawled INTEGER NOT NULL DEFAULT 0,
  urls_discovered INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (site_id) REFERENCES sites(site_id) ON DELETE CASCADE
);

CREATE TABLE pages (
  page_id INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id INTEGER NOT NULL,
  url TEXT NOT NULL,
  source TEXT NOT NULL DEFAULT 'spider' CHECK (source IN ('sitemap','spider','both')),
  http_status INTEGER NULL,
  content_type TEXT NULL,
  final_url TEXT NULL,
  title TEXT NULL,
  meta_description TEXT NULL,
  h1_count INTEGER NOT NULL DEFAULT 0,
  h1_first TEXT NULL,
  canonical TEXT NULL,
  robots_meta TEXT NULL,
  word_count INTEGER NOT NULL DEFAULT 0,
  schema_count INTEGER NOT NULL DEFAULT 0,
  heading_count INTEGER NOT NULL DEFAULT 0,
  heading_outline TEXT NULL,
  heading_hierarchy_skips INTEGER NOT NULL DEFAULT 0,
  load_error TEXT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (run_id, url),
  FOREIGN KEY (run_id) REFERENCES crawl_runs(run_id) ON DELETE CASCADE
);
CREATE INDEX idx_pages_run_status ON pages(run_id, http_status);

CREATE TABLE page_links (
  link_id INTEGER PRIMARY KEY AUTOINCREMENT,
  page_id INTEGER NOT NULL,
  href TEXT NOT NULL,
  link_text TEXT NULL,
  link_type TEXT NOT NULL CHECK (link_type IN ('internal','external')),
  discovered_url TEXT NULL,
  status_checked INTEGER NOT NULL DEFAULT 0,
  http_status INTEGER NULL,
  FOREIGN KEY (page_id) REFERENCES pages(page_id) ON DELETE CASCADE
);
CREATE INDEX idx_page_links_page_type ON page_links(page_id, link_type);

CREATE TABLE page_assets (
  asset_id INTEGER PRIMARY KEY AUTOINCREMENT,
  page_id INTEGER NOT NULL,
  asset_type TEXT NOT NULL CHECK (asset_type IN ('css','js','image')),
  url TEXT NOT NULL,
  alt_text TEXT NULL,
  has_width INTEGER NULL,
  has_height INTEGER NULL,
  FOREIGN KEY (page_id) REFERENCES pages(page_id) ON DELETE CASCADE
);
CREATE INDEX idx_page_assets_type ON page_assets(asset_type);

CREATE TABLE page_headings (
  heading_id INTEGER PRIMARY KEY AUTOINCREMENT,
  page_id INTEGER NOT NULL,
  heading_level INTEGER NOT NULL,
  heading_text TEXT NULL,
  position INTEGER NOT NULL,
  FOREIGN KEY (page_id) REFERENCES pages(page_id) ON DELETE CASCADE
);
CREATE INDEX idx_page_headings_page ON page_headings(page_id, position);

CREATE TABLE checks (
  check_id INTEGER PRIMARY KEY AUTOINCREMENT,
  code TEXT NOT NULL UNIQUE,
  label TEXT NOT NULL,
  check_type TEXT NOT NULL CHECK (check_type IN ('field_length','field_required','equals','regex','css_selector','numeric_min','numeric_max')),
  field_name TEXT NULL,
  selector TEXT NULL,
  pattern TEXT NULL,
  min_value INTEGER NULL,
  max_value INTEGER NULL,
  severity TEXT NOT NULL DEFAULT 'warning' CHECK (severity IN ('info','opportunity','warning','issue')),
  enabled INTEGER NOT NULL DEFAULT 1,
  pass_message TEXT NULL,
  fail_message TEXT NULL
);

CREATE TABLE check_results (
  result_id INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id INTEGER NOT NULL,
  page_id INTEGER NOT NULL,
  check_id INTEGER NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('green','amber','red')),
  measured_value TEXT NULL,
  message TEXT NULL,
  FOREIGN KEY (run_id) REFERENCES crawl_runs(run_id) ON DELETE CASCADE,
  FOREIGN KEY (page_id) REFERENCES pages(page_id) ON DELETE CASCADE,
  FOREIGN KEY (check_id) REFERENCES checks(check_id) ON DELETE CASCADE
);
CREATE INDEX idx_check_results_status ON check_results(run_id, status);

INSERT INTO sites (name, base_url, sitemap_url)
VALUES ('Brimbank Bin Hire', 'https://brimbankbinhire.com.au/', 'https://brimbankbinhire.com.au/sitemap.xml')
ON CONFLICT(base_url) DO UPDATE SET sitemap_url=excluded.sitemap_url;

INSERT INTO checks (code,label,check_type,field_name,min_value,max_value,severity,pass_message,fail_message) VALUES
('title_required','Page title exists','field_required','title',NULL,NULL,'issue','Title found','Missing page title'),
('title_length','Page title length','field_length','title',30,60,'opportunity','Title length is OK','Title is too short or too long'),
('description_required','Meta description exists','field_required','meta_description',NULL,NULL,'issue','Meta description found','Missing meta description'),
('description_length','Meta description length','field_length','meta_description',80,160,'opportunity','Meta description length is OK','Meta description is too short or too long'),
('h1_required','H1 exists','numeric_min','h1_count',1,NULL,'issue','H1 found','Missing H1'),
('h1_single','Only one H1','numeric_max','h1_count',NULL,1,'warning','Single H1 found','Multiple H1 tags found'),
('canonical_required','Canonical exists','field_required','canonical',NULL,NULL,'warning','Canonical found','Missing canonical'),
('schema_present','Schema JSON-LD exists','numeric_min','schema_count',1,NULL,'opportunity','Schema detected','No JSON-LD schema detected'),
('http_status_ok','HTTP status is 200','equals','http_status',200,NULL,'issue','HTTP status is 200','HTTP status is not 200'),
('content_word_count_min','Minimum visible word count','numeric_min','word_count',250,NULL,'warning','Page has enough visible copy','Visible copy may be thin'),
('heading_hierarchy_no_skips','Heading hierarchy has no skipped levels','numeric_max','heading_hierarchy_skips',NULL,0,'warning','Heading levels do not skip','Heading levels skip, for example H2 to H4')
ON CONFLICT(code) DO UPDATE SET label=excluded.label;

INSERT INTO checks (code,label,check_type,selector,min_value,severity,pass_message,fail_message) VALUES
('custom_phone_visible','Visible phone number appears somewhere','css_selector','a[href^="tel:"]',1,'info','Phone link found','No tel: link found')
ON CONFLICT(code) DO UPDATE SET selector=excluded.selector;
