-- Recruitment Management System v1.0 - Schema
CREATE DATABASE IF NOT EXISTS `vishnucm_rms` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `vishnucm_rms`;

CREATE TABLE roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL,
  permissions TEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  role_id INT NOT NULL,
  status TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

CREATE TABLE countries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  code VARCHAR(10) NULL,
  status TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE companies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  logo VARCHAR(255) NULL,
  country_id INT NULL,
  website VARCHAR(255) NULL,
  description TEXT NULL,
  status TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  status TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NULL,
  company_id INT NOT NULL,
  country_id INT NOT NULL,
  department_id INT NOT NULL,
  description TEXT NULL,
  vacancies INT DEFAULT 1,
  experience VARCHAR(100) NULL,
  qualification VARCHAR(200) NULL,
  salary VARCHAR(100) NULL,
  expiry_date DATE NULL,
  status ENUM('draft','published','closed') DEFAULT 'draft',
  created_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (company_id) REFERENCES companies(id),
  FOREIGN KEY (country_id) REFERENCES countries(id),
  FOREIGN KEY (department_id) REFERENCES departments(id)
) ENGINE=InnoDB;

CREATE TABLE candidates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_id INT NOT NULL,
  full_name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL,
  phone VARCHAR(30) NULL,
  address TEXT NULL,
  education TEXT NULL,
  experience TEXT NULL,
  skills TEXT NULL,
  resume VARCHAR(255) NULL,
  cover_letter TEXT NULL,
  status ENUM('new','under_review','shortlisted','rejected','selected') DEFAULT 'new',
  notes TEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE cms_pages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(50) UNIQUE NOT NULL,
  title VARCHAR(200) NULL,
  content TEXT NULL,
  image VARCHAR(255) NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  skey VARCHAR(100) UNIQUE NOT NULL,
  svalue TEXT NULL
) ENGINE=InnoDB;

-- Seed data
INSERT INTO roles (name, permissions) VALUES
('Super Admin','dashboard,jobs,candidates,companies,countries,departments,users,roles,reports,cms,settings'),
('Admin','dashboard,jobs,candidates,companies,countries,departments,reports,cms,settings'),
('HR Executive','dashboard,jobs,candidates,reports'),
('Recruiter','dashboard,jobs,candidates');

-- Default password: admin123
INSERT INTO users (name,email,password,role_id,status) VALUES
('Super Admin','admin@rms.local','$2y$12$UfuUCc5GfPCY1Hhh6VLBm.N2iwjLNkcTMS/ZnT5TuynVQbWff61b.',1,1);

INSERT INTO countries (name,code) VALUES ('United Arab Emirates','AE'),('India','IN'),('Saudi Arabia','SA'),('United States','US');
INSERT INTO departments (name) VALUES ('HR'),('Marketing'),('Warehouse'),('Sales'),('Finance'),('Operations'),('IT');
INSERT INTO companies (name,country_id,website,description) VALUES
('Acme Corp',1,'https://acme.example','Global logistics leader'),
('Globex Ltd',2,'https://globex.example','Enterprise software');

INSERT INTO jobs (title,company_id,country_id,department_id,description,vacancies,experience,qualification,salary,expiry_date,status,created_by) VALUES
('Senior Recruiter',1,1,1,'Lead recruitment cycle.',2,'3-5 years','Bachelor Degree','Negotiable',DATE_ADD(CURDATE(),INTERVAL 30 DAY),'published',1),
('Marketing Executive',2,2,2,'Own campaigns end to end.',1,'2+ years','MBA Marketing','Negotiable',DATE_ADD(CURDATE(),INTERVAL 45 DAY),'published',1);

INSERT INTO cms_pages (slug,title,content) VALUES
('banner','Build Your Career With Us','Discover roles that match your ambitions.'),
('about','About Careers','We are hiring passionate professionals across the globe.'),
('why','Why Join Us','Great culture, growth opportunities, and global exposure.'),
('contact','Contact Information','careers@rms.local | +971-000-0000');

INSERT INTO settings (skey,svalue) VALUES
('site_name','Recruitment Management System'),
('site_logo',''),
('smtp_host',''),('smtp_port','587'),('smtp_user',''),('smtp_pass',''),('smtp_from','no-reply@rms.local'),
('upload_max_mb','5'),('allowed_ext','pdf,doc,docx');