-- Horizonhire MVP schema
DROP DATABASE IF EXISTS horizonhire;
CREATE DATABASE horizonhire CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE horizonhire;

-- Users
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role ENUM('employee','employer','admin') NOT NULL DEFAULT 'employee',
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  phone VARCHAR(40) NULL,
  earnwise_account_number VARCHAR(40) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Jobs
CREATE TABLE jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employer_id INT NOT NULL,
  title VARCHAR(160) NOT NULL,
  category VARCHAR(100) NULL,
  description TEXT NOT NULL,
  rate_per_hour DECIMAL(10,2) NOT NULL DEFAULT 35.25,
  weekly_hours_min INT NOT NULL DEFAULT 20,
  weekly_hours_max INT NOT NULL DEFAULT 45,
  is_remote TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('open','closed') NOT NULL DEFAULT 'open',
  company_terms TEXT NULL,
  job_terms TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (employer_id) REFERENCES users(id)
);

-- Applications
CREATE TABLE applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_id INT NOT NULL,
  employee_id INT NOT NULL,
  status ENUM(
    'applied','under_review','interview_requested','interview_submitted',
    'offer_made','offer_accepted','rejected','hired'
  ) NOT NULL DEFAULT 'applied',
  cover_letter TEXT NULL,
  resume_path VARCHAR(255) NULL,
  terms_accepted TINYINT(1) NOT NULL DEFAULT 0,
  offer_letter_path VARCHAR(255) NULL,
  offer_issued_at DATETIME NULL,
  offer_accepted_at DATETIME NULL,
  hired_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (job_id) REFERENCES jobs(id),
  FOREIGN KEY (employee_id) REFERENCES users(id)
);

-- Interview
CREATE TABLE interview_questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_id INT NOT NULL,
  question_text TEXT NOT NULL,
  qtype ENUM('text','file') NOT NULL DEFAULT 'text',
  required TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (job_id) REFERENCES jobs(id)
);

CREATE TABLE interview_answers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_id INT NOT NULL,
  question_id INT NOT NULL,
  answer_text TEXT NULL,
  file_path VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (application_id) REFERENCES applications(id),
  FOREIGN KEY (question_id) REFERENCES interview_questions(id)
);

-- Chat
CREATE TABLE messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_id INT NOT NULL,
  sender_id INT NOT NULL,
  message_text TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (application_id) REFERENCES applications(id),
  FOREIGN KEY (sender_id) REFERENCES users(id)
);

-- Tasks
CREATE TABLE tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_id INT NOT NULL,
  title VARCHAR(160) NOT NULL,
  description TEXT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  position INT NOT NULL DEFAULT 1,
  FOREIGN KEY (job_id) REFERENCES jobs(id)
);

CREATE TABLE task_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  task_id INT NOT NULL,
  application_id INT NOT NULL,
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  can_start_after DATETIME NOT NULL,
  status ENUM('locked','started','submitted','approved') NOT NULL DEFAULT 'locked',
  submission_path VARCHAR(255) NULL,
  submitted_at DATETIME NULL,
  approved_at DATETIME NULL,
  amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (task_id) REFERENCES tasks(id)
);

-- Wallet & Payments
CREATE TABLE wallets (
  user_id INT PRIMARY KEY,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type ENUM('credit','debit') NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  source VARCHAR(50) NOT NULL,
  reference VARCHAR(100) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE withdrawals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  bank_name VARCHAR(120) NOT NULL DEFAULT 'Earnwise FCU',
  account_number VARCHAR(40) NOT NULL,
  status ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Seed users
INSERT INTO users (role,name,email,password_hash,phone,earnwise_account_number) VALUES
('employer','Horizonhire HR','employer@horizonhire.test', SHA2('password',256),'',NULL),
('employee','John Doe','employee@horizonhire.test', SHA2('password',256),'','EWFCU-0011223344');

INSERT INTO wallets (user_id,balance) VALUES (1,0.00),(2,0.00);

-- Seed jobs (your positions)
INSERT INTO jobs (employer_id,title,category,description,company_terms,job_terms) VALUES
(1,'Software Engineer','Software','Develop innovative software solutions remotely at $35.25/hr. Flexible hours (20–45/wk).',
 'You must accept Horizonhire company terms & privacy.','You must follow job-specific guidelines and instructions.'),
(1,'Marketing Manager','Marketing','Lead our marketing team and drive business growth (remote, flexible).',NULL,NULL),
(1,'Data Entry Clerk','Data Entry','Accurately enter data into our database.',NULL,NULL),
(1,'Customer Support Representative','Support','Provide exceptional customer service.',NULL,NULL),
(1,'Sales Representative','Sales','Drive sales growth and build relationships.',NULL,NULL),
(1,'Call Center Director/Manager','Call Center','Lead and optimize call center operations.',NULL,NULL),
(1,'Clerical Admin','Administration','General office/clerical tasks.',NULL,NULL),
(1,'Administrative Clerk/Assistant','Administration','Assist with admin tasks and scheduling.',NULL,NULL),
(1,'Medical Billing and Coding','Healthcare','Handle billing and coding remotely.',NULL,NULL),
(1,'Payroll Clerk','Finance','Process payroll accurately and on time.',NULL,NULL),
(1,'Bookkeeping','Finance','Maintain financial records and reconciliations.',NULL,NULL),
(1,'Dispatcher','Logistics','Coordinate dispatch operations.',NULL,NULL),
(1,'Medical Biller/Coder','Healthcare','Process medical bills and codes.',NULL,NULL),
(1,'Typist Clerk','Administration','Fast and accurate typing tasks.',NULL,NULL);

-- A simple interview template for job #1
INSERT INTO interview_questions (job_id,question_text,qtype,required) VALUES
(1,'Tell us about a complex project you shipped. What was your role?', 'text', 1),
(1,'Upload a sample (PDF/ZIP) of your code or portfolio.', 'file', 0);

-- Two progressive tasks for job #1
INSERT INTO tasks (job_id,title,description,amount,position) VALUES
(1,'Onboarding & Environment Setup','Follow the checklist and submit a zip of your setup notes/screens.',50.00,1),
(1,'Ship Feature X','Implement the specified feature and submit a zip or link for review.',80.00,2);