const Database = require('better-sqlite3');
const path = require('path');
const bcrypt = require('bcryptjs');
const fs = require('fs');

const dbDir = path.join(__dirname, '../database');
if (!fs.existsSync(dbDir)) fs.mkdirSync(dbDir);

const db = new Database(path.join(dbDir, 'pes_system.db'));

// 1. Create Core Tables
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    role TEXT CHECK(role IN ('admin', 'chro', 'head_pms', 'manager', 'team_lead', 'member')) DEFAULT 'member',
    department TEXT,
    designation TEXT,
    reporting_manager_id INTEGER,
    location TEXT,
    employee_code TEXT,
    grade TEXT,
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

  CREATE TABLE IF NOT EXISTS focus_areas (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    priority INTEGER DEFAULT 1,
    status TEXT DEFAULT 'active'
  );

  CREATE TABLE IF NOT EXISTS rocks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    owner_id INTEGER NOT NULL,
    focus_area_id INTEGER,
    quarter TEXT,
    year INTEGER,
    status TEXT CHECK(status IN ('on_track', 'at_risk', 'off_track', 'completed')),
    progress INTEGER DEFAULT 0,
    priority INTEGER DEFAULT 1,
    due_date DATETIME,
    created_by INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_date DATETIME,
    FOREIGN KEY (owner_id) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    task_type TEXT DEFAULT 'task',
    status TEXT DEFAULT 'not_started',
    priority TEXT DEFAULT 'medium',
    assignee_id INTEGER,
    assigner_id INTEGER,
    reviewer_id INTEGER,
    rock_id INTEGER,
    key_result_id INTEGER,
    focus_area_id INTEGER,
    progress INTEGER DEFAULT 0,
    start_date DATETIME,
    due_date DATETIME,
    completed_date DATETIME,
    requires_approval INTEGER DEFAULT 0,
    approval_status TEXT DEFAULT 'not_required',
    reminder_sent INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (assignee_id) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS scorecard_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    perspective TEXT CHECK(perspective IN ('financial', 'customer', 'internal_process', 'learning_growth')),
    display_order INTEGER,
    is_active INTEGER DEFAULT 1
  );

  CREATE TABLE IF NOT EXISTS scorecard_metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER,
    name TEXT NOT NULL,
    target_value REAL,
    warning_threshold REAL,
    unit TEXT,
    owner_id INTEGER,
    is_active INTEGER DEFAULT 1,
    FOREIGN KEY (category_id) REFERENCES scorecard_categories(id)
  );

  CREATE TABLE IF NOT EXISTS scorecard_entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    metric_id INTEGER,
    value REAL,
    week_starting DATE,
    entered_by INTEGER,
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (metric_id) REFERENCES scorecard_metrics(id)
  );
`);

// 2. Pre-populate 18 Strategic Focus Areas
const focusAreas = [
  "HRMS implementation", "Analysis of Productivity", "Grade Matrix & Nomenclature",
  "Fixed vs Contract Manpower", "Female Headcount Increase", "Manpower Productivity",
  "Revenue Per Person", "Attrition Analysis", "C&B Benchmarking", "Role & Reporting Structure",
  "Standardization of Processes", "100% HR Documentation", "Automation Identification",
  "100% Background Verification", "Internal Mobility/Second Line", "Fresher Hiring Program",
  "Cost Optimization", "Best Industry Benchmarking"
];

const insertFA = db.prepare('INSERT INTO focus_areas (name) VALUES (?)');
focusAreas.forEach(area => insertFA.run(area));

// 3. Create Admin User (Govind)
const salt = bcrypt.genSaltSync(10);
const hashedPass = bcrypt.hashSync('admin123', salt);

db.prepare(`
  INSERT OR IGNORE INTO users (email, password, first_name, last_name, role, department, designation)
  VALUES ('govind@sheetaljewellery.com', ?, 'Govind', 'CHRO', 'chro', 'PES', 'Head - PES')
`).run(hashedPass);

console.log("Database initialized successfully with 18 Focus Areas and Admin user.");