-- PYMA HR · Schema MySQL
-- Rulează acest fișier o singură dată în phpMyAdmin sau via SSH

SET NAMES utf8mb4;
SET time_zone = '+02:00';

CREATE DATABASE IF NOT EXISTS pyma_hr CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE pyma_hr;

-- ── LOCAȚII ──────────────────────────────────────────────────────────────────
CREATE TABLE locations (
  id       INT AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  slug     VARCHAR(50)  NOT NULL UNIQUE,
  active   TINYINT DEFAULT 1,
  created  DATETIME DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO locations (name, slug) VALUES
  ('Casablanca', 'casablanca'),
  ('Plopii fără Soț', 'plopii'),
  ('Pyma Kids', 'pyma-kids'),
  ('Balance Pilates', 'pilates');

-- ── POSTURI ──────────────────────────────────────────────────────────────────
CREATE TABLE job_roles (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(150) NOT NULL,
  slug        VARCHAR(80)  NOT NULL UNIQUE,
  location_id INT,
  active      TINYINT DEFAULT 1,
  created     DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (location_id) REFERENCES locations(id)
);

INSERT INTO job_roles (name, slug, location_id) VALUES
  ('Șef de Sală', 'sef-de-sala', 1);

-- ── ACTIVITĂȚI ───────────────────────────────────────────────────────────────
CREATE TABLE activities (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  job_role_id INT NOT NULL,
  position    INT DEFAULT 0,
  title       VARCHAR(300) NOT NULL,
  FOREIGN KEY (job_role_id) REFERENCES job_roles(id)
);

-- ── SARCINI ──────────────────────────────────────────────────────────────────
CREATE TABLE tasks (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  activity_id INT NOT NULL,
  nr          INT NOT NULL,
  text        TEXT NOT NULL,
  FOREIGN KEY (activity_id) REFERENCES activities(id)
);

-- ── UTILIZATORI ──────────────────────────────────────────────────────────────
CREATE TABLE users (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(150) NOT NULL,
  email        VARCHAR(200) NOT NULL UNIQUE,
  password     VARCHAR(255) NOT NULL,
  role         ENUM('admin','manager','staff') DEFAULT 'staff',
  job_role_id  INT,
  location_id  INT,
  active       TINYINT DEFAULT 1,
  created      DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_login   DATETIME,
  FOREIGN KEY (job_role_id) REFERENCES job_roles(id),
  FOREIGN KEY (location_id) REFERENCES locations(id)
);

-- Admin default: paul@pyma.ro / pyma2024 (schimbă după instalare!)
INSERT INTO users (name, email, password, role) VALUES
  ('Paul Pădurariu', 'paul@pyma.ro', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uHonGiXMm', 'admin'),
  ('Elena Pădurariu', 'elena@pyma.ro', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uHonGiXMm', 'admin');

-- ── EVALUĂRI ─────────────────────────────────────────────────────────────────
CREATE TABLE evaluations (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  user_id       INT NOT NULL,
  job_role_id   INT NOT NULL,
  event_name    VARCHAR(200),
  event_date    DATE,
  period_label  VARCHAR(100),
  type          ENUM('checklist','omerth','both') DEFAULT 'both',
  status        ENUM('draft','submitted','validated') DEFAULT 'draft',
  notion_synced TINYINT DEFAULT 0,
  notion_page_id VARCHAR(200),
  submitted_at  DATETIME,
  validated_at  DATETIME,
  validated_by  INT,
  created       DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (job_role_id) REFERENCES job_roles(id),
  FOREIGN KEY (validated_by) REFERENCES users(id)
);

-- ── RĂSPUNSURI CHECKLIST ─────────────────────────────────────────────────────
CREATE TABLE checklist_responses (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  evaluation_id INT NOT NULL,
  task_id       INT NOT NULL,
  checked       TINYINT DEFAULT 0,
  checked_at    DATETIME,
  UNIQUE KEY uniq_eval_task (evaluation_id, task_id),
  FOREIGN KEY (evaluation_id) REFERENCES evaluations(id) ON DELETE CASCADE,
  FOREIGN KEY (task_id) REFERENCES tasks(id)
);

-- ── RĂSPUNSURI OMERTH ────────────────────────────────────────────────────────
CREATE TABLE omerth_responses (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  evaluation_id INT NOT NULL,
  task_id       INT NOT NULL,
  score_auto    TINYINT CHECK (score_auto BETWEEN 1 AND 5),
  score_mgr     TINYINT CHECK (score_mgr BETWEEN 1 AND 5),
  notes         TEXT,
  UNIQUE KEY uniq_eval_task (evaluation_id, task_id),
  FOREIGN KEY (evaluation_id) REFERENCES evaluations(id) ON DELETE CASCADE,
  FOREIGN KEY (task_id) REFERENCES tasks(id)
);

-- ── SETĂRI GLOBALE ───────────────────────────────────────────────────────────
CREATE TABLE settings (
  k VARCHAR(100) PRIMARY KEY,
  v TEXT
);

INSERT INTO settings (k, v) VALUES
  ('notion_token', ''),
  ('notion_db_evaluari', ''),
  ('app_name', 'PYMA HR'),
  ('app_url', 'https://hr.pyma.ro');