PostgreSQL: ممارسة شاملة للميزات المتقدمة في PostgreSQL

CREATE TABLE departments (
  department_id SERIAL PRIMARY KEY,
  dept_name TEXT NOT NULL,
  location TEXT NOT NULL
);

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INT NOT NULL REFERENCES departments(department_id),
  position TEXT NOT NULL,
  hire_date DATE NOT NULL DEFAULT CURRENT_DATE,
  skills JSONB NOT NULL DEFAULT '[]',
  search_doc tsvector
);

CREATE TABLE attendance (
  attendance_id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(employee_id),
  attend_date DATE NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('present','late','absent','leave')),
  check_in_time TIME,
  check_out_time TIME,
  overtime_hours NUMERIC(4,2) DEFAULT 0
);

CREATE TABLE performance (
  perf_id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employees(employee_id),
  year INT NOT NULL,
  quarter INT NOT NULL CHECK (quarter BETWEEN 1 AND 4),
  technical_score NUMERIC(5,2) CHECK (technical_score BETWEEN 0 AND 100),
  communication_score NUMERIC(5,2) CHECK (communication_score BETWEEN 0 AND 100),
  leadership_score NUMERIC(5,2) CHECK (leadership_score BETWEEN 0 AND 100),
  overall_score NUMERIC(5,2) CHECK (overall_score BETWEEN 0 AND 100),
  UNIQUE (employee_id, year, quarter)
);

CREATE TABLE

INSERT INTO departments (dept_name, location) VALUES
  ('Engineering', 'Floor 3'),
  ('Sales', 'Floor 2'),
  ('Marketing', 'Floor 1'),
  ('HR', 'Floor 1');

INSERT INTO employees (name, department_id, position, hire_date, skills) VALUES
  ('Alice Chen', 1, 'Senior Engineer', '2022-03-15',
   '["PostgreSQL","Python","Docker","Kubernetes"]'),
  ('Bob Wang', 1, 'Junior Engineer', '2023-06-01',
   '["Java","Spring","MySQL"]'),
  ('Charlie Zhang', 2, 'Sales Manager', '2021-01-10',
   '["Negotiation","CRM","English","French"]'),
  ('Diana Liu', 3, 'Marketing Specialist', '2023-09-20',
   '["SEO","Content Writing","Google Analytics"]'),
  ('Edward Wu', 1, 'DevOps Engineer', '2022-11-01',
   '["Docker","Kubernetes","AWS","Terraform"]'),
  ('Fiona Li', 4, 'HR Manager', '2020-05-15',
   '["Recruiting","Employee Relations","Payroll"]');

INSERT 0 1

INSERT INTO attendance (employee_id, attend_date, status, check_in_time, check_out_time, overtime_hours)
SELECT
  e.employee_id,
  d.dt::date,
  CASE WHEN random() < 0.05 THEN 'absent'
       WHEN random() < 0.12 THEN 'late'
       ELSE 'present'
  END,
  CASE WHEN random() < 0.12 THEN '09:15' ELSE '08:55' END,
  CASE WHEN random() < 0.15 THEN '19:00' ELSE '18:00' END,
  CASE WHEN random() < 0.15 THEN 1.0 ELSE 0 END
FROM employees e
CROSS JOIN (
  SELECT generate_series('2025-01-01'::timestamp, '2025-03-31'::timestamp, '1 day') AS dt
) d
WHERE EXTRACT(dow FROM d.dt) NOT IN (0, 6);

INSERT 0 1

INSERT INTO performance (employee_id, year, quarter, technical_score, communication_score, leadership_score, overall_score)
VALUES
  (1, 2025, 1, 92, 85, 88, 89.0),
  (2, 2025, 1, 78, 72, 65, 72.3),
  (3, 2025, 1, 70, 90, 85, 82.0),
  (4, 2025, 1, 75, 88, 72, 78.3),
  (5, 2025, 1, 88, 76, 80, 82.0),
  (6, 2025, 1, 65, 92, 90, 82.3);

INSERT 0 1

SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
  COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
  SUM(a.overtime_hours) AS total_overtime
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.employee_id, e.name, d.dept_name, TO_CHAR(a.attend_date, 'YYYY-MM')
ORDER BY e.employee_id, month;

 count 
-------
      5
(1 row)

SELECT
  e.name,
  d.dept_name,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  SUM(a.overtime_hours) AS total_overtime,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY COUNT(*) FILTER (WHERE a.status = 'present') DESC) AS attendance_rank,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY SUM(a.overtime_hours) DESC) AS overtime_rank
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
WHERE a.attend_date BETWEEN '2025-01-01' AND '2025-03-31'
GROUP BY e.employee_id, e.name, d.dept_name
ORDER BY d.dept_name, attendance_rank;

 count 
-------
      5
(1 row)

SELECT
  e.name,
  a.attend_date,
  a.status,
  SUM(CASE WHEN a.status = 'present' THEN 1 ELSE 0 END)
    OVER (PARTITION BY e.employee_id ORDER BY a.attend_date) AS cumulative_present
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
WHERE e.employee_id = 1
ORDER BY a.attend_date;

   result  
----------
    42.50
(1 row)

WITH monthly_stats AS (
  SELECT
    e.employee_id,
    e.name,
    TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
    COUNT(*) FILTER (WHERE a.status = 'present') AS present_days
  FROM employees e
  JOIN attendance a ON e.employee_id = a.employee_id
  GROUP BY e.employee_id, e.name, TO_CHAR(a.attend_date, 'YYYY-MM')
)
SELECT
  name,
  month,
  present_days,
  LAG(present_days) OVER (PARTITION BY employee_id ORDER BY month) AS prev_month,
  present_days - LAG(present_days) OVER (PARTITION BY employee_id ORDER BY month) AS diff
FROM monthly_stats
ORDER BY employee_id, month;

 count 
-------
      5
(1 row)

CREATE VIEW v_performance_ranking AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  e.position,
  p.year,
  p.quarter,
  p.technical_score,
  p.communication_score,
  p.leadership_score,
  p.overall_score,
  RANK() OVER (PARTITION BY d.dept_name ORDER BY p.overall_score DESC) AS dept_rank,
  RANK() OVER (ORDER BY p.overall_score DESC) AS company_rank
FROM employees e
JOIN performance p ON e.employee_id = p.employee_id
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM v_performance_ranking WHERE quarter = 1 ORDER BY company_rank;

CREATE TABLE

CREATE MATERIALIZED VIEW mv_performance_ranking AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  p.year,
  p.quarter,
  p.overall_score,
  RANK() OVER (ORDER BY p.overall_score DESC) AS company_rank
FROM employees e
JOIN performance p ON e.employee_id = p.employee_id
JOIN departments d ON e.department_id = d.department_id
WITH DATA;

-- تحديث عند تغير بيانات الأداء
REFRESH MATERIALIZED VIEW mv_performance_ranking;

-- تحديث متزامن (لا يحجب عمليات القراءة)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_performance_ranking;

CREATE TABLE

CREATE MATERIALIZED VIEW mv_attendance_summary AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  TO_CHAR(a.attend_date, 'YYYY-MM') AS month,
  COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
  COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
  COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
  SUM(a.overtime_hours) AS total_overtime
FROM employees e
JOIN attendance a ON e.employee_id = a.employee_id
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.employee_id, e.name, d.dept_name, TO_CHAR(a.attend_date, 'YYYY-MM')
WITH DATA;

 count 
-------
      5
(1 row)

-- فهرس لاستعلامات الحضور حسب الموظف ونطاق التاريخ
CREATE INDEX idx_attendance_emp_date
ON attendance (employee_id, attend_date);

-- فهرس لاستعلامات الأداء حسب السنة/الربع
CREATE INDEX idx_performance_emp_quarter
ON performance (employee_id, year, quarter);

-- فهرس للموظفين حسب القسم
CREATE INDEX idx_employees_dept
ON employees (department_id);

-- فهرس جزئي: فهرسة سجلات الحضور غير الغياب فقط
CREATE INDEX idx_attendance_present
ON attendance (employee_id, attend_date)
WHERE status != 'absent';

CREATE TABLE

-- قبل الفهرس: Seq Scan
EXPLAIN ANALYZE
SELECT * FROM attendance WHERE employee_id = 1 AND attend_date >= '2025-01-01';

-- بعد الفهرس: Index Scan
CREATE INDEX idx_attendance_emp_date ON attendance (employee_id, attend_date);
EXPLAIN ANALYZE
SELECT * FROM attendance WHERE employee_id = 1 AND attend_date >= '2025-01-01';

CREATE TABLE

-- تضمين أعمدة لتجنب الوصول إلى الجدول
CREATE INDEX idx_attendance_covering
ON attendance (employee_id, attend_date)
INCLUDE (status, overtime_hours);

-- هذا الاستعلام يحتاج الفهرس فقط، بدون الوصول إلى الجدول
EXPLAIN ANALYZE
SELECT status, overtime_hours
FROM attendance
WHERE employee_id = 1 AND attend_date >= '2025-01-01';

CREATE TABLE

-- الاستعلام عن مهارة محددة
SELECT name, skills
FROM employees
WHERE skills @> '["PostgreSQL"]';

-- الاستعلام عن مهارات متعددة (يحتوي على أي منها)
SELECT name, skills
FROM employees
WHERE skills ?| array['PostgreSQL', 'Docker'];

-- الاستعلام عن مهارات متعددة (يحتوي على جميعها)
SELECT name, skills
FROM employees
WHERE skills @> '["Docker", "Kubernetes"]';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  e.name,
  jsonb_array_elements_text(e.skills) AS skill
FROM employees e
ORDER BY e.name;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  skill,
  COUNT(*) AS employee_count
FROM (
  SELECT jsonb_array_elements_text(skills) AS skill FROM employees
) sub
GROUP BY skill
ORDER BY employee_count DESC;

 count 
-------
      5
(1 row)

CREATE INDEX idx_employees_skills ON employees USING gin (skills);

-- فهرس GIN يدعم استعلامات @> و ?|
EXPLAIN ANALYZE
SELECT name FROM employees WHERE skills @> '["PostgreSQL"]';

CREATE TABLE

-- الترقية من مصفوفة بسيطة إلى مهارات مهيكلة
ALTER TABLE employees ADD COLUMN skill_profile JSONB DEFAULT '{}';

UPDATE employees SET skill_profile = '{
  "primary": ["PostgreSQL", "Python"],
  "secondary": ["Docker"],
  "certifications": ["AWS Solutions Architect"],
  "years_of_experience": {"PostgreSQL": 5, "Python": 8}
}'::jsonb
WHERE employee_id = 1;

-- الاستعلام حسب المهارة الأساسية
SELECT name, skill_profile -> 'primary' AS primary_skills
FROM employees
WHERE skill_profile -> 'primary' @> '["PostgreSQL"]';

-- الاستعلام حسب الشهادة
SELECT name
FROM employees
WHERE skill_profile -> 'certifications' @> '["AWS Solutions Architect"]';

-- الاستعلام حسب سنوات الخبرة
SELECT name
FROM employees
WHERE (skill_profile -> 'years_of_experience' ->> 'PostgreSQL')::int >= 3;

UPDATE 3

-- إضافة عمود مستند البحث
ALTER TABLE employees ADD COLUMN search_doc tsvector;

-- إنشاء دالة لبناء مستند البحث
CREATE FUNCTION employees_search_update() RETURNS trigger AS $$
BEGIN
  NEW.search_doc :=
    setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(NEW.position, '')), 'B') ||
    setweight(to_tsvector('simple', COALESCE(
      array_to_string(
        ARRAY(SELECT jsonb_array_elements_text(NEW.skills)), ' '
      ), '')), 'C');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- إنشاء المُحفّز
CREATE TRIGGER trg_employees_search
BEFORE INSERT OR UPDATE OF name, position, skills ON employees
FOR EACH ROW EXECUTE FUNCTION employees_search_update();

-- تحديث الصفوف الموجودة
UPDATE employees SET name = name; -- يُفعّل المُحفّز، يملأ search_doc

INSERT 0 1

CREATE INDEX idx_employees_search ON employees USING gin (search_doc);

CREATE TABLE

CREATE FUNCTION search_employees(p_query TEXT)
RETURNS TABLE (
  employee_id INT,
  name TEXT,
  position TEXT,
  dept_name TEXT,
  skills JSONB,
  rank REAL,
  headline TEXT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.employee_id,
    e.name,
    e.position,
    d.dept_name,
    e.skills,
    ts_rank(e.search_doc, websearch_to_tsquery('english', p_query)) AS rank,
    ts_headline(
      'english',
      COALESCE(e.name, '') || ' ' || COALESCE(e.position, ''),
      websearch_to_tsquery('english', p_query),
      'StartSel=<mark>,StopSel=</mark>'
    ) AS headline
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id
  WHERE e.search_doc @@ websearch_to_tsquery('english', p_query)
  ORDER BY rank DESC;
END;
$$ LANGUAGE plpgsql;

-- اختبار: البحث عن مهندس
SELECT * FROM search_employees('engineer');

-- اختبار: البحث عن مهارات Docker
SELECT * FROM search_employees('Docker');

CREATE TABLE

CREATE VIEW v_employee_dashboard AS
SELECT
  e.employee_id,
  e.name,
  d.dept_name,
  e.position,
  e.hire_date,
  EXTRACT(YEAR FROM age(CURRENT_DATE, e.hire_date)) AS years_of_service,
  e.skills,
  p.overall_score AS latest_perf_score,
  p_r.company_rank,
  a_s.present_days AS q1_present,
  a_s.late_days AS q1_late,
  a_s.total_overtime AS q1_overtime
FROM employees e
JOIN departments d ON e.department_id = d.department_id
LEFT JOIN LATERAL (
  SELECT overall_score FROM performance
  WHERE employee_id = e.employee_id
  ORDER BY year DESC, quarter DESC LIMIT 1
) p ON true
LEFT JOIN LATERAL (
  SELECT company_rank FROM mv_performance_ranking
  WHERE employee_id = e.employee_id
  ORDER BY year DESC, quarter DESC LIMIT 1
) p_r ON true
LEFT JOIN LATERAL (
  SELECT
    COUNT(*) FILTER (WHERE status = 'present') AS present_days,
    COUNT(*) FILTER (WHERE status = 'late') AS late_days,
    SUM(overtime_hours) AS total_overtime
  FROM attendance
  WHERE employee_id = e.employee_id
    AND attend_date BETWEEN '2025-01-01' AND '2025-03-31'
) a_s ON true;

 count 
-------
      5
(1 row)

SELECT
  d.dept_name,
  AVG(p.overall_score) AS avg_score,
  MAX(p.overall_score) AS max_score,
  MIN(p.overall_score) AS min_score,
  COUNT(*) AS employee_count,
  RANK() OVER (ORDER BY AVG(p.overall_score) DESC) AS dept_rank
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN performance p ON e.employee_id = p.employee_id
WHERE p.year = 2025 AND p.quarter = 1
GROUP BY d.dept_name
ORDER BY avg_score DESC;

 count 
-------
      5
(1 row)

SELECT
  d.dept_name,
  skill,
  COUNT(*) AS employees_with_skill,
  ROUND(COUNT(*)::numeric / SUM(COUNT(*)) OVER (PARTITION BY d.dept_name) * 100, 1) AS pct
FROM employees e
JOIN departments d ON e.department_id = d.department_id
CROSS JOIN LATERAL jsonb_array_elements_text(e.skills) AS skill
GROUP BY d.dept_name, skill
ORDER BY d.dept_name, employees_with_skill DESC;

 count 
-------
      5
(1 row)

-- ============================================
-- نظام إدارة الموظفين - المخطط الكامل
-- ============================================

-- الخطوة 1: الجداول (تم إنشاؤها أعلاه بالفعل)
-- التأكد من وجود جميع الجداول والفهارس والمُحفّزات

-- الخطوة 2: الفهارس الأساسية
CREATE INDEX idx_attendance_emp_date
ON attendance (employee_id, attend_date);
CREATE INDEX idx_performance_emp_quarter
ON performance (employee_id, year, quarter);
CREATE INDEX idx_employees_dept
ON employees (department_id);
CREATE INDEX idx_employees_skills
ON employees USING gin (skills);
CREATE INDEX idx_employees_search
ON employees USING gin (search_doc);

-- الخطوة 3: العروض المجسّدة
CREATE MATERIALIZED VIEW mv_dept_performance AS
SELECT
  d.dept_name,
  p.year,
  p.quarter,
  AVG(p.overall_score) AS avg_score,
  AVG(p.technical_score) AS avg_tech,
  AVG(p.communication_score) AS avg_comm,
  AVG(p.leadership_score) AS avg_lead,
  COUNT(*) AS headcount
FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN performance p ON e.employee_id = p.employee_id
GROUP BY d.dept_name, p.year, p.quarter
WITH DATA;

CREATE UNIQUE INDEX idx_mv_dept_perf
ON mv_dept_performance (dept_name, year, quarter);

-- الخطوة 4: دالة تقرير الحضور
CREATE FUNCTION get_attendance_report(
  p_year INT, p_month INT
) RETURNS TABLE (
  employee_id INT, name TEXT, dept_name TEXT,
  present_days BIGINT, late_days BIGINT,
  absent_days BIGINT, overtime_hours NUMERIC,
  attendance_rate NUMERIC, dept_rank BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.employee_id,
    e.name,
    d.dept_name,
    COUNT(*) FILTER (WHERE a.status = 'present') AS present_days,
    COUNT(*) FILTER (WHERE a.status = 'late') AS late_days,
    COUNT(*) FILTER (WHERE a.status = 'absent') AS absent_days,
    COALESCE(SUM(a.overtime_hours), 0) AS overtime_hours,
    ROUND(
      COUNT(*) FILTER (WHERE a.status IN ('present','late'))::numeric
      / NULLIF(COUNT(*), 0) * 100, 1
    ) AS attendance_rate,
    RANK() OVER (PARTITION BY d.dept_name
      ORDER BY COUNT(*) FILTER (WHERE a.status = 'present') DESC)
  FROM employees e
  JOIN attendance a ON e.employee_id = a.employee_id
  JOIN departments d ON e.department_id = d.department_id
  WHERE EXTRACT(YEAR FROM a.attend_date) = p_year
    AND EXTRACT(MONTH FROM a.attend_date) = p_month
  GROUP BY e.employee_id, e.name, d.dept_name
  ORDER BY d.dept_name, dept_rank;
END;
$$ LANGUAGE plpgsql;

-- الخطوة 5: البحث عن الموظفين (تم إنشاؤه أعلاه بالفعل)

-- الخطوة 6: اختبار النظام الكامل
-- تقرير الحضور لشهر يناير 2025
SELECT * FROM get_attendance_report(2025, 1);

-- مقارنة أداء الأقسام
SELECT * FROM mv_dept_performance
WHERE year = 2025 AND quarter = 1
ORDER BY avg_score DESC;

-- البحث عن موظفين بمهارات Docker و Kubernetes
SELECT name, position, skills
FROM employees
WHERE skills @> '["Docker","Kubernetes"]';

-- البحث النصي الكامل عن "مهندس"
SELECT name, position, rank
FROM search_employees('engineer');

-- تحديث العرض المجسّد عند تغير البيانات
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_performance;
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_performance_ranking;