-- Phase D: Assets inventory + categories + policies/acknowledgements.
-- Optional employee_documents deferred (storage-heavy; not required for exit criteria).

CREATE TABLE IF NOT EXISTS hr_assets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  asset_tag VARCHAR(64) NULL,
  category VARCHAR(64) NOT NULL DEFAULT 'equipment',
  description_html MEDIUMTEXT NULL,
  status ENUM('available','assigned','returned','lost','retired') NOT NULL DEFAULT 'available',
  assigned_employee_id INT NULL,
  assigned_at DATETIME NULL,
  returned_at DATETIME 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,
  KEY idx_hr_assets_emp (assigned_employee_id, status),
  CONSTRAINT fk_hr_assets_emp FOREIGN KEY (assigned_employee_id) REFERENCES employees(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS hr_asset_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  description VARCHAR(512) 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_hr_asset_cat_name (name)
);

INSERT IGNORE INTO hr_asset_categories (name, description) VALUES
  ('equipment', 'Laptops, phones, and other gear'),
  ('furniture', 'Desks, chairs, and office furniture'),
  ('access', 'Badges, keys, and access cards');

CREATE TABLE IF NOT EXISTS hr_policies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  body_html MEDIUMTEXT NULL,
  version INT NOT NULL DEFAULT 1,
  requires_ack TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  storage_key VARCHAR(512) 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
);

CREATE TABLE IF NOT EXISTS hr_policy_acknowledgements (
  id INT AUTO_INCREMENT PRIMARY KEY,
  policy_id INT NOT NULL,
  employee_id INT NOT NULL,
  acknowledged_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_policy_ack (policy_id, employee_id),
  CONSTRAINT fk_pol_ack_pol FOREIGN KEY (policy_id) REFERENCES hr_policies(id) ON DELETE CASCADE,
  CONSTRAINT fk_pol_ack_emp FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

INSERT IGNORE INTO permission_modules (module_key, label, module_group, sort_order) VALUES
  ('hr_assets', 'HR Assets', 'HR', 27);

INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed)
SELECT wr.id, pm.module_key, pa.action_key, 1
FROM workspace_roles wr
CROSS JOIN permission_modules pm
CROSS JOIN permission_actions pa
WHERE wr.slug = 'super-admin'
  AND pm.module_key = 'hr_assets'
  AND pa.action_key IN ('read', 'create', 'update', 'delete', 'export');

-- Employees with employees.read can read policies/assets and acknowledge policies.
INSERT IGNORE INTO role_permissions (role_id, module_key, action_key, allowed)
SELECT DISTINCT rp.role_id, 'hr_assets', 'read', 1
FROM role_permissions rp
WHERE rp.module_key = 'employees' AND rp.action_key = 'read' AND rp.allowed = 1;