-- ============================================================
-- PAP Practice Database - Oracle Live SQL (livesql.oracle.com)
-- تدريب روابي: انسخي هذا السكربت كامل في Oracle Live SQL ونفذيه
-- ============================================================

-- 1) الجداول
CREATE TABLE departments (
  dept_code    VARCHAR2(10) PRIMARY KEY,
  dept_name    VARCHAR2(50),
  cost_center  VARCHAR2(10)
);

CREATE TABLE employees (
  emp_id       NUMBER PRIMARY KEY,
  emp_name     VARCHAR2(60),
  dept_code    VARCHAR2(10) REFERENCES departments(dept_code),
  position     VARCHAR2(40),
  basic_salary NUMBER(10,2),
  hire_date    DATE,
  shift        VARCHAR2(15)
);

CREATE TABLE costs (
  record_id  NUMBER PRIMARY KEY,
  emp_id     NUMBER,
  dept_code  VARCHAR2(10),
  cost_date  DATE,
  cost_type  VARCHAR2(20),
  amount     NUMBER(12,2)
);

-- 2) بيانات الأقسام
INSERT INTO departments VALUES ('OPS','PAP Operations','CC-4101');
INSERT INTO departments VALUES ('MNT','PAP Maintenance','CC-4102');
INSERT INTO departments VALUES ('LAB','PAP Laboratory','CC-4103');
INSERT INTO departments VALUES ('UTL','PAP Utilities','CC-4104');
INSERT INTO departments VALUES ('HSE','PAP HSE','CC-4105');

-- 3) عينة موظفين
INSERT INTO employees VALUES (10001,'Mohammed AlGhamdi','OPS','Plant Operator',8500,TO_DATE('15-03-2019','DD-MM-YYYY'),'Rotating-A');
INSERT INTO employees VALUES (10002,'Ahmed AlZahrani','OPS','Shift Supervisor',14200,TO_DATE('02-07-2016','DD-MM-YYYY'),'Rotating-B');
INSERT INTO employees VALUES (10003,'Khalid AlQahtani','OPS','Control Room Operator',11000,TO_DATE('20-01-2021','DD-MM-YYYY'),'Rotating-C');
INSERT INTO employees VALUES (10004,'Fahad AlOtaibi','MNT','Mechanical Technician',9200,TO_DATE('11-09-2018','DD-MM-YYYY'),'Day');
INSERT INTO employees VALUES (10005,'Abdullah AlShehri','MNT','Electrical Technician',8800,TO_DATE('05-05-2020','DD-MM-YYYY'),'Day');
INSERT INTO employees VALUES (10006,'Saad AlDossari','MNT','Maintenance Engineer',16500,TO_DATE('28-02-2015','DD-MM-YYYY'),'Day');
INSERT INTO employees VALUES (10007,'Nasser AlMutairi','LAB','Lab Analyst',9500,TO_DATE('14-11-2022','DD-MM-YYYY'),'Rotating-A');
INSERT INTO employees VALUES (10008,'Bandar AlHarbi','LAB','Senior Chemist',15000,TO_DATE('09-04-2017','DD-MM-YYYY'),'Day');
INSERT INTO employees VALUES (10009,'Turki AlAmri','UTL','Utility Operator',7800,TO_DATE('23-08-2023','DD-MM-YYYY'),'Rotating-B');
INSERT INTO employees VALUES (10010,'Yousef AlJuhani','HSE','Safety Officer',10500,TO_DATE('17-06-2021','DD-MM-YYYY'),'Day');

-- 4) عينة تكاليف (لاحظي: فيها مشاكل مقصودة!)
-- رواتب يناير
INSERT INTO costs VALUES (1,10001,'OPS',TO_DATE('25-01-2026','DD-MM-YYYY'),'Salary',12466);
INSERT INTO costs VALUES (2,10002,'OPS',TO_DATE('25-01-2026','DD-MM-YYYY'),'Salary',20823);
INSERT INTO costs VALUES (3,10004,'MNT',TO_DATE('25-01-2026','DD-MM-YYYY'),'Salary',13493);
INSERT INTO costs VALUES (4,10007,'LAB',TO_DATE('25-01-2026','DD-MM-YYYY'),'Salary',13932);
INSERT INTO costs VALUES (5,10010,'HSE',TO_DATE('25-01-2026','DD-MM-YYYY'),'Salary',15400);
-- أوفرتايم يناير
INSERT INTO costs VALUES (6,10001,'OPS',TO_DATE('25-01-2026','DD-MM-YYYY'),'Overtime',531.25);
INSERT INTO costs VALUES (7,10004,'MNT',TO_DATE('25-01-2026','DD-MM-YYYY'),'Overtime',862.50);
-- إعاشة ومواصلات يناير
INSERT INTO costs VALUES (8,10001,'OPS',TO_DATE('25-01-2026','DD-MM-YYYY'),'Catering',396);
INSERT INTO costs VALUES (9,10001,'OPS',TO_DATE('25-01-2026','DD-MM-YYYY'),'Transportation',350);
INSERT INTO costs VALUES (10,10004,'MNT',TO_DATE('25-01-2026','DD-MM-YYYY'),'Catering',396);
-- فبراير
INSERT INTO costs VALUES (11,10001,'OPS',TO_DATE('25-02-2026','DD-MM-YYYY'),'Salary',12466);
INSERT INTO costs VALUES (12,10002,'OPS',TO_DATE('25-02-2026','DD-MM-YYYY'),'Salary',20823);
INSERT INTO costs VALUES (13,10004,'MNT',TO_DATE('25-02-2026','DD-MM-YYYY'),'Salary',13493);
INSERT INTO costs VALUES (14,10001,'OPS',TO_DATE('25-02-2026','DD-MM-YYYY'),'Overtime',708.33);
-- مايو: شهر الـ Turnaround (لاحظي القفزة)
INSERT INTO costs VALUES (15,10004,'MNT',TO_DATE('25-05-2026','DD-MM-YYYY'),'Overtime',3450.00);
INSERT INTO costs VALUES (16,10005,'MNT',TO_DATE('25-05-2026','DD-MM-YYYY'),'Overtime',2887.50);
INSERT INTO costs VALUES (17,10001,'OPS',TO_DATE('25-05-2026','DD-MM-YYYY'),'Overtime',1993.75);
-- ===== مشاكل مقصودة للتدريب =====
-- (أ) سجل مكرر تمامًا (نفس بيانات record 15 بمعرف مختلف)
INSERT INTO costs VALUES (18,10004,'MNT',TO_DATE('25-05-2026','DD-MM-YYYY'),'Overtime',3450.00);
-- (ب) مبلغ NULL
INSERT INTO costs VALUES (19,10007,'LAB',TO_DATE('25-05-2026','DD-MM-YYYY'),'Catering',NULL);
-- (ج) مبلغ سالب غلط
INSERT INTO costs VALUES (20,10009,'UTL',TO_DATE('25-05-2026','DD-MM-YYYY'),'Transportation',-350);
-- (د) موظف غير موجود في جدول الموظفين
INSERT INTO costs VALUES (21,99001,'OPS',TO_DATE('25-05-2026','DD-MM-YYYY'),'Salary',9800);
-- (هـ) نوع تكلفة بحروف غلط
INSERT INTO costs VALUES (22,10010,'HSE',TO_DATE('25-05-2026','DD-MM-YYYY'),'overtime',450);

COMMIT;

-- ============================================================
-- التمارين: حاولي تحلين قبل ما تشوفين الحلول تحت
-- ============================================================

-- تمرين 1: إجمالي التكلفة لكل قسم لكل شهر
-- تمرين 2: الموظفون اللي ما لهم أي سجل تكلفة (LEFT JOIN)
-- تمرين 3: سجلات التكلفة اللي صاحبها غير موجود في جدول الموظفين
-- تمرين 4: كشف السجلات المكررة بـ ROW_NUMBER
-- تمرين 5: التراكمي الشهري لكل قسم بـ Window Function
-- تمرين 6: فرق الأوفرتايم عن الشهر السابق بـ LAG
-- تمرين 7: توحيد cost_type (overtime = Overtime) وجمعها صح
-- تمرين 8: المبالغ السالبة أو الفارغة (تقرير جودة بيانات)

-- ============================================================
-- الحلول
-- ============================================================

-- حل 1:
SELECT dept_code, TO_CHAR(cost_date,'YYYY-MM') AS mn, SUM(NVL(amount,0)) AS total
FROM costs
GROUP BY dept_code, TO_CHAR(cost_date,'YYYY-MM')
ORDER BY dept_code, mn;

-- حل 2:
SELECT e.emp_id, e.emp_name
FROM employees e
LEFT JOIN costs c ON e.emp_id = c.emp_id
WHERE c.emp_id IS NULL;

-- حل 3 (سجلات يتيمة):
SELECT c.*
FROM costs c
LEFT JOIN employees e ON c.emp_id = e.emp_id
WHERE e.emp_id IS NULL;

-- حل 4 (المكرر):
SELECT * FROM (
  SELECT c.*,
         ROW_NUMBER() OVER (
           PARTITION BY emp_id, cost_date, UPPER(TRIM(cost_type)), amount
           ORDER BY record_id
         ) AS rn
  FROM costs c
) WHERE rn > 1;

-- حل 5 (تراكمي):
SELECT dept_code, TO_CHAR(cost_date,'YYYY-MM') AS mn,
       SUM(SUM(NVL(amount,0))) OVER (
         PARTITION BY dept_code
         ORDER BY TO_CHAR(cost_date,'YYYY-MM')
       ) AS running_total
FROM costs
GROUP BY dept_code, TO_CHAR(cost_date,'YYYY-MM');

-- حل 6 (مقارنة MoM للأوفرتايم):
WITH ot AS (
  SELECT dept_code, TO_CHAR(cost_date,'YYYY-MM') AS mn, SUM(amount) AS ot_cost
  FROM costs
  WHERE UPPER(TRIM(cost_type)) = 'OVERTIME'
  GROUP BY dept_code, TO_CHAR(cost_date,'YYYY-MM')
)
SELECT dept_code, mn, ot_cost,
       LAG(ot_cost) OVER (PARTITION BY dept_code ORDER BY mn) AS prev_month,
       ot_cost - LAG(ot_cost) OVER (PARTITION BY dept_code ORDER BY mn) AS change
FROM ot;

-- حل 7 (توحيد التصنيف):
SELECT INITCAP(TRIM(cost_type)) AS clean_type, SUM(NVL(amount,0)) AS total
FROM costs
GROUP BY INITCAP(TRIM(cost_type))
ORDER BY total DESC;

-- حل 8 (تقرير جودة):
SELECT record_id, emp_id, cost_type, amount,
       CASE
         WHEN amount IS NULL THEN 'NULL amount'
         WHEN amount < 0 THEN 'Negative amount'
       END AS issue
FROM costs
WHERE amount IS NULL OR amount < 0;
