CREATE TABLE IF NOT EXISTS projects (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL,
  project_type_id INT NOT NULL,
  status ENUM('active', 'on-hold', 'completed', 'inactive') NOT NULL DEFAULT 'active',
  owner_id INT NULL,
  website_link VARCHAR(512) NULL,
  package_id VARCHAR(64) NULL,
  auto_task TINYINT(1) NOT NULL DEFAULT 0,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  priority ENUM('urgent', 'high', 'medium', 'low') NOT NULL DEFAULT 'medium',
  progress TINYINT UNSIGNED NOT NULL DEFAULT 0,
  description TEXT NULL,
  client_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uk_projects_slug (slug),
  KEY idx_projects_type (project_type_id),
  KEY idx_projects_owner (owner_id),
  CONSTRAINT fk_projects_type FOREIGN KEY (project_type_id) REFERENCES project_types (id),
  CONSTRAINT fk_projects_owner FOREIGN KEY (owner_id) REFERENCES employees (id),
  CONSTRAINT fk_projects_package FOREIGN KEY (package_id) REFERENCES marketing_packages (id)
);

CREATE TABLE IF NOT EXISTS project_collaborators (
  project_id INT NOT NULL,
  employee_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (project_id, employee_id),
  CONSTRAINT fk_pc_project FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE,
  CONSTRAINT fk_pc_employee FOREIGN KEY (employee_id) REFERENCES employees (id)
);

CREATE TABLE IF NOT EXISTS tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  title VARCHAR(500) NOT NULL,
  stage VARCHAR(128) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'open',
  priority ENUM('urgent', 'high', 'medium', 'low') NOT NULL DEFAULT 'medium',
  assignee_id INT NULL,
  due_date DATE NOT NULL,
  description TEXT NULL,
  checklist JSON NULL,
  comments JSON NULL,
  files JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_tasks_project (project_id),
  KEY idx_tasks_assignee (assignee_id),
  CONSTRAINT fk_tasks_project FOREIGN KEY (project_id) REFERENCES projects (id),
  CONSTRAINT fk_tasks_assignee FOREIGN KEY (assignee_id) REFERENCES employees (id)
);