-- Basic database structure for matching platform
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS companies;
CREATE TABLE companies (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address TEXT,
  website TEXT,
  description TEXT,
  industry VARCHAR(255),
  location TEXT,
  employees INTEGER,
  email VARCHAR(255),
  phone VARCHAR(50),
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(20) NOT NULL CHECK (role IN ('client','talent','agent','admin')),
  company_name VARCHAR(255),
  company_id INTEGER REFERENCES companies(id),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 細分化されたスキルタグ
DROP TABLE IF EXISTS skills;
CREATE TABLE skills (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL,
  category_large VARCHAR(50),
  category_medium VARCHAR(50)
);

DROP TABLE IF EXISTS projects;
CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  client_id INTEGER NOT NULL REFERENCES users(id),
  company_id INTEGER REFERENCES companies(id),
  title TEXT NOT NULL,
  description TEXT,
  required_skills JSON,
  preferred_skills JSON,
  location VARCHAR(10),
  duration VARCHAR(50),
  hours_per_week INTEGER,
  start_date DATE,
  end_date DATE,
  compensation_min INTEGER,
  compensation_max INTEGER,
  currency VARCHAR(3),
  compensation_type VARCHAR(20),
  status VARCHAR(20) CHECK (status IN ('draft','active','paused','completed')),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS candidate_profiles;
CREATE TABLE candidate_profiles (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  experienced_skills JSON,
  desired_skills JSON,
  location VARCHAR(10),
  duration VARCHAR(50),
  experience JSON,
  portfolio JSON,
  hours_per_week INTEGER,
  start_date DATE,
  rate_min INTEGER,
  rate_max INTEGER,
  currency VARCHAR(3),
  rate_type VARCHAR(20) NOT NULL DEFAULT 'monthly',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS contracts;
CREATE TABLE contracts (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id),
  contractor_id INTEGER NOT NULL REFERENCES candidate_profiles(id),
  status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft','pending','active','partner_approved','completed','cancelled','deleted')),
  start_date DATE,
  end_date DATE,
  total_amount INTEGER,
  payment_terms TEXT,
  progress INTEGER DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS matches;
CREATE TABLE matches (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id),
  candidate_id INTEGER NOT NULL REFERENCES candidate_profiles(id),
  score INTEGER,
  status VARCHAR(20),
  candidate_status VARCHAR(20) DEFAULT 'pending',
  client_status VARCHAR(20) DEFAULT 'pending',
  message TEXT,
  contract_id INTEGER REFERENCES contracts(id),
  breakdown JSON,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS interviews;
CREATE TABLE interviews (
  id SERIAL PRIMARY KEY,
  match_id INTEGER NOT NULL REFERENCES projects(id),
  talent_user_id INTEGER NOT NULL REFERENCES users(id),
  scheduled_at TIMESTAMP NOT NULL,
  duration_minutes INTEGER,
  step VARCHAR(50),
  interviewer VARCHAR(255),
  location VARCHAR(255),
  meeting_url TEXT,
  notes TEXT,
  status VARCHAR(20) CHECK (status IN ('scheduled','completed','canceled')),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
  id SERIAL PRIMARY KEY,
  project_id INTEGER REFERENCES projects(id),
  match_id INTEGER REFERENCES matches(id),
  sender_id INTEGER NOT NULL REFERENCES users(id),
  recipient_id INTEGER NOT NULL REFERENCES users(id),
  content TEXT NOT NULL,
  read_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS notifications;
CREATE TABLE notifications (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  message TEXT NOT NULL,
  is_read BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS user_settings;
CREATE TABLE user_settings (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL UNIQUE REFERENCES users(id),
  settings JSON,
  header_setting JSON,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS favorite_projects;
CREATE TABLE favorite_projects (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  project_id INTEGER NOT NULL REFERENCES projects(id),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, project_id)
);

DROP TABLE IF EXISTS applications;
CREATE TABLE applications (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id),
  user_id INTEGER NOT NULL REFERENCES users(id),
  message TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS favorite_candidates;
CREATE TABLE favorite_candidates (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  candidate_id INTEGER NOT NULL REFERENCES candidate_profiles(user_id),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, candidate_id)
);

DROP TABLE IF EXISTS project_history;
CREATE TABLE project_history (
  id SERIAL PRIMARY KEY,
  candidate_id INTEGER NOT NULL REFERENCES users(id),
  project_id INTEGER REFERENCES projects(id),
  title TEXT,
  description TEXT,
  skills JSON,
  start_date DATE,
  end_date DATE,
  role TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS reviews;
CREATE TABLE reviews (
  id SERIAL PRIMARY KEY,
  match_id INTEGER REFERENCES matches(id),
  reviewer_id INTEGER NOT NULL REFERENCES users(id),
  reviewee_type VARCHAR(20) CHECK (reviewee_type IN ('project','candidate')),
  reviewee_id INTEGER NOT NULL,
  rating INTEGER CHECK (rating >= 1 AND rating <= 5),
  comment TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS otp_codes;
CREATE TABLE otp_codes (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  code VARCHAR(6) NOT NULL,
  expires_at TIMESTAMP NOT NULL,
  used BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Configuration options such as terms and privacy policy
DROP TABLE IF EXISTS options;
CREATE TABLE options (
  id VARCHAR(50) PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

DROP TABLE IF EXISTS password_reset_tokens;
CREATE TABLE password_reset_tokens (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  token VARCHAR(64) NOT NULL,
  expires_at TIMESTAMP NOT NULL,
  used BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
SET FOREIGN_KEY_CHECKS = 1;
