-- Migration 0002: subcategories + employees

-- Add subcategory_id to outflow_categories (parent-child: type → category → subcategory)
ALTER TABLE outflow_categories ADD COLUMN parent_id INTEGER DEFAULT NULL;
ALTER TABLE outflow_categories ADD COLUMN is_salary INTEGER DEFAULT 0; -- flag salary categories

-- Employees table
CREATE TABLE IF NOT EXISTS employees (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  phone TEXT,
  joining_date TEXT,
  designation TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Link outflows to employees (for salary entries)
ALTER TABLE outflows ADD COLUMN employee_id INTEGER DEFAULT NULL;
ALTER TABLE outflows ADD COLUMN subcategory_id INTEGER DEFAULT NULL;

CREATE INDEX IF NOT EXISTS idx_employees_active ON employees(is_active);
CREATE INDEX IF NOT EXISTS idx_outflows_employee ON outflows(employee_id);
CREATE INDEX IF NOT EXISTS idx_categories_parent ON outflow_categories(parent_id);

-- Mark Salaries category as salary type
UPDATE outflow_categories SET is_salary = 1 WHERE name = 'Salaries';

-- Seed a few demo employees
INSERT OR IGNORE INTO employees (name, phone, joining_date, designation) VALUES
  ('Ramesh Kumar', '9876543001', '2022-01-15', 'Receptionist'),
  ('Priya Nair', '9876543002', '2021-06-01', 'Lab Technician'),
  ('Suresh Babu', '9876543003', '2023-03-10', 'Pharmacist'),
  ('Meena Devi', '9876543004', '2020-09-01', 'Nurse');
