-- Crawlie admin backend — MySQL schema
-- Import this once into a fresh database before deploying the PHP backend.
-- Example:  mysql -u youruser -p yourdatabase < schema.sql

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

CREATE TABLE IF NOT EXISTS admin_sessions (
  token CHAR(64) PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS blog_posts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(255) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  excerpt TEXT,
  content LONGTEXT,
  published TINYINT(1) NOT NULL DEFAULT 0,
  post_date DATE,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_published_date (published, post_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Optional activity log for the admin dashboard — every server-side crawl/fetch
-- performed via api/crawl.php or api/fetch.php gets a best-effort row here.
-- Purely informational; the crawler still works fully if this insert ever fails
-- (e.g. table temporarily locked), since it's wrapped in a try/catch.
CREATE TABLE IF NOT EXISTS crawl_jobs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  target_url VARCHAR(768) NOT NULL,
  mode VARCHAR(20) NOT NULL DEFAULT 'discover',
  source VARCHAR(20) DEFAULT NULL,
  pages_scanned INT UNSIGNED DEFAULT 0,
  items_found INT UNSIGNED DEFAULT 0,
  duration_ms INT UNSIGNED DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed post so the blog isn't empty on first deploy (mirrors the localStorage demo post).
INSERT IGNORE INTO blog_posts (slug, title, excerpt, content, published, post_date) VALUES (
  'welcome-to-the-blog',
  'Welcome to the Crawlie Blog',
  'This is a sample post to show how the blog looks. Edit or delete it from the admin panel.',
  'This is a sample post created automatically so you can see how the blog looks before you write anything of your own.\n\nOpen the admin panel to edit this post, delete it, or add new ones. Posts you publish here are stored in MySQL and visible to every visitor of your site.',
  1,
  CURDATE()
);
