-- CT1 Technologies Blog — Database Setup
-- Run this once to initialise your database.
-- Update the user/password to match your config/db.php

-- Create database (run as root/admin)
CREATE DATABASE IF NOT EXISTS ct1_blog
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE ct1_blog;

-- Categories
CREATE TABLE IF NOT EXISTS categories (
  id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(120)  NOT NULL,
  slug      VARCHAR(130)  NOT NULL UNIQUE,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Posts
CREATE TABLE IF NOT EXISTS posts (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id   INT UNSIGNED NULL,
  title         VARCHAR(255) NOT NULL,
  slug          VARCHAR(270) NOT NULL UNIQUE,
  excerpt       TEXT         NULL,
  body          LONGTEXT     NOT NULL,
  cover_image   VARCHAR(500) NULL        COMMENT 'Relative path, e.g. /images/blog/my-post.webp',
  status        ENUM('draft','published') NOT NULL DEFAULT 'draft',
  published_at  DATETIME     NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_post_category FOREIGN KEY (category_id)
    REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin users (single-tenant, simple bcrypt passwords)
CREATE TABLE IF NOT EXISTS admin_users (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(80)  NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed a default admin — password: ct1admin2025 (CHANGE THIS IMMEDIATELY)
INSERT IGNORE INTO admin_users (username, password_hash)
VALUES ('admin', '$2y$12$8hkv0J5cJc7A8WZ3VXqhP.vJjLMpz2K4IZuJk7dREqGXo3f.YNJTq');

-- Seed a couple of sample categories
INSERT IGNORE INTO categories (name, slug) VALUES
  ('IT News',      'it-news'),
  ('Cybersecurity','cybersecurity'),
  ('Microsoft 365','microsoft-365'),
  ('Tips & Guides','tips-guides');