-- 001_create_jobs_table.sql
-- This script creates the main 'jobs' table for the SkillRunner platform.

CREATE TABLE IF NOT EXISTS `jobs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NOT NULL,
  `category` VARCHAR(100) NOT NULL,
  `skills` VARCHAR(255) NOT NULL COMMENT 'Comma-separated list of skills',
  `budget` DECIMAL(10, 2) NOT NULL,
  `deadline` DATE NOT NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'open' COMMENT 'e.g., open, reserved, in_progress, completed',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `client_id` INT COMMENT 'Foreign key to users table, can be added later'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;