-- Audit log table
CREATE TABLE audit_log (
  log_id      BIGSERIAL PRIMARY KEY,
  table_name  TEXT NOT NULL,
  operation   TEXT NOT NULL, -- INSERT, UPDATE, DELETE
  old_data    JSONB,
  new_data    JSONB,
  changed_by  TEXT DEFAULT CURRENT_USER,
  changed_at  TIMESTAMP DEFAULT NOW()
);

-- Generic audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log(table_name, operation, new_data)
    VALUES (TG_TABLE_NAME, 'INSERT', TO_JSONB(NEW));
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log(table_name, operation, old_data, new_data)
    VALUES (TG_TABLE_NAME, 'UPDATE', TO_JSONB(OLD), TO_JSONB(NEW));
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log(table_name, operation, old_data)
    VALUES (TG_TABLE_NAME, 'DELETE', TO_JSONB(OLD));
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Apply to employees table
CREATE TRIGGER employees_audit
  AFTER INSERT OR UPDATE OR DELETE ON employees
  FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();