CREATE TABLE IF NOT EXISTS departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(6) NOT NULL,
  description TEXT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  UNIQUE KEY uq_departments_code_active (code, deleted_at),
  INDEX idx_departments_deleted (deleted_at),
  INDEX idx_departments_status (status)
);

CREATE TABLE IF NOT EXISTS employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  dept_id INT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_employees_dept FOREIGN KEY (dept_id) REFERENCES departments(id),
  INDEX idx_employees_dept (dept_id)
);

CREATE TABLE IF NOT EXISTS user_saved_filters (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  module VARCHAR(50) NOT NULL,
  name VARCHAR(255) NOT NULL,
  conditions JSON NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_usf_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_usf_user_module (user_id, module)
);

CREATE TABLE IF NOT EXISTS user_table_preferences (
  user_id INT NOT NULL,
  module VARCHAR(50) NOT NULL,
  columns_visible JSON NOT NULL,
  column_order JSON NOT NULL,
  applied_filter_ids JSON NOT NULL DEFAULT ('[]'),
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, module),
  CONSTRAINT fk_utp_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

INSERT INTO departments (name, code, description, status) VALUES
  ('Engineering', 'ENG', 'Product engineering and platform development', 'active'),
  ('Marketing', 'MKT', 'Marketing, brand, and growth', 'active'),
  ('Product', 'PRD', 'Product management and strategy', 'active'),
  ('Human Resources', 'HR', 'People operations and talent', 'active'),
  ('Finance', 'FIN', 'Finance and accounting', 'inactive');