-- Designations table
CREATE TABLE IF NOT EXISTS designations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  department_id INT,
  parent_id INT NULL,
  description TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
  FOREIGN KEY (parent_id) REFERENCES designations(id) ON DELETE SET NULL,
  
  INDEX idx_department_id (department_id),
  INDEX idx_parent_id (parent_id),
  INDEX idx_is_active (is_active)
);

-- Insert some sample designations
INSERT INTO designations (name, department_id, parent_id, description) VALUES
('Commander', 1, NULL, 'Unit Commander'),
('Deputy Commander', 1, 1, 'Deputy Unit Commander'),
('Executive Officer', 1, 1, 'Executive Officer'),
('Chief of Staff', 1, 1, 'Chief of Staff'),
('Operations Officer', 1, 2, 'Operations Department Head'),
('Logistics Officer', 1, 2, 'Logistics Department Head'),
('Administrative Officer', 1, 2, 'Administrative Department Head'),
('Technical Officer', 1, 2, 'Technical Department Head'),
('Senior Officer', 1, 3, 'Senior Level Officer'),
('Junior Officer', 1, 3, 'Junior Level Officer'),
('Senior NCO', 1, 4, 'Senior Non-Commissioned Officer'),
('Junior NCO', 1, 4, 'Junior Non-Commissioned Officer'),
('Petty Officer', 1, 4, 'Petty Officer'),
('Leading Seaman', 1, 4, 'Leading Seaman'),
('Able Seaman', 1, 4, 'Able Seaman'),
('Ordinary Seaman', 1, 4, 'Ordinary Seaman');