CREATE DATABASE IF NOT EXISTS dailycareers CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE dailycareers;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(200) NOT NULL,
  email VARCHAR(200) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('user','admin') NOT NULL DEFAULT 'user',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  heading VARCHAR(255) NOT NULL,
  company VARCHAR(255) NOT NULL,
  location VARCHAR(255) NOT NULL,
  category VARCHAR(100) NOT NULL,
  closing_date DATE NOT NULL,
  short_description VARCHAR(500) NOT NULL,
  full_description TEXT NOT NULL,
  apply_link VARCHAR(1000) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE password_resets (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT NOT NULL,
 token VARCHAR(255) NOT NULL,
 used TINYINT DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- sample admin (change password hash to a real one)
INSERT INTO users (full_name,email,password,role) VALUES ('Admin','admin@dailycareers.co.za', '$2y$10$abcdefghijklmnopqrstuv', 'admin');