Final SQL Assignment (Task Release)

-- Example structure (adapt to your own domain)
CREATE TABLE IF NOT EXISTS departments (
    dept_id     SERIAL PRIMARY KEY,
    dept_name   VARCHAR(100) NOT NULL UNIQUE,
    location    VARCHAR(200),
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS employees (
    emp_id        SERIAL PRIMARY KEY,
    full_name     VARCHAR(200) NOT NULL,
    dept_id       INT NOT NULL REFERENCES departments(dept_id),
    hire_date     DATE NOT NULL CHECK (hire_date >= '2000-01-01'),
    salary        NUMERIC(10,2) CHECK (salary >= 0),
    status        VARCHAR(20) DEFAULT 'active'
                  CHECK (status IN ('active', 'on_leave', 'terminated')),
    full_name_len INT GENERATED ALWAYS AS (LENGTH(full_name)) STORED
);

-- ALTER TABLE examples with explanatory comments
-- Adding an email column that was missed in the initial design
ALTER TABLE employees ADD COLUMN IF NOT EXISTS email VARCHAR(255);

-- Enforcing email uniqueness once the column exists
ALTER TABLE employees ADD CONSTRAINT uq_employee_email UNIQUE (email);

-- Adding a non-empty location constraint to departments
ALTER TABLE departments ADD CONSTRAINT chk_location_not_empty
    CHECK (location IS NULL OR LENGTH(TRIM(location)) > 0);

-- Re-runnable: truncate in child-first order
TRUNCATE employees, departments RESTART IDENTITY CASCADE;

-- Parent table insert
INSERT INTO departments (dept_name, location) VALUES
    ('Engineering',   'Building A'),
    ('Marketing',     'Building B'),
    ('Finance',       'Building C'),
    ('HR',            'Building A'),
    ('Operations',    'Building D')
ON CONFLICT DO NOTHING;

-- Child table insert: FK resolved via subquery, no hardcoded IDs
INSERT INTO employees (full_name, dept_id, hire_date, salary, status, email)
VALUES
    ('Alice Johnson',
     (SELECT dept_id FROM departments WHERE dept_name = 'Engineering'),
     '2022-03-15', 85000.00, 'active', 'alice@example.com'),
    ('Bob Smith',
     (SELECT dept_id FROM departments WHERE dept_name = 'Marketing'),
     '2021-07-01', 72000.00, 'active', 'bob@example.com')
ON CONFLICT DO NOTHING;

-- INSERT INTO ... SELECT example
INSERT INTO employees (full_name, dept_id, hire_date, salary, status, email)
SELECT
    'Contractor: ' || dept_name,
    dept_id,
    CURRENT_DATE,
    55000.00,
    'active',
    LOWER(dept_name) || '.contractor@example.com'
FROM departments
WHERE location = 'Building A'
ON CONFLICT DO NOTHING;

-- CTE + INSERT example
WITH new_staff AS (
    SELECT
        'Intern ' || dept_name AS full_name,
        dept_id,
        CURRENT_DATE AS hire_date,
        25000.00     AS salary,
        'active'     AS status,
        LOWER(dept_name) || '.intern@example.com' AS email
    FROM departments
    WHERE dept_name IN ('HR', 'Finance')
)
INSERT INTO employees (full_name, dept_id, hire_date, salary, status, email)
SELECT full_name, dept_id, hire_date, salary, status, email
FROM new_staff
ON CONFLICT DO NOTHING;

-- Query 1: Basic — active employees hired after 2021, newest first
SELECT emp_id, full_name, hire_date, salary
FROM employees
WHERE status = 'active'
  AND hire_date > '2021-01-01'
ORDER BY hire_date DESC
LIMIT 10;

-- Query 2: Multi-table JOIN (adapt to your own 3+ tables)
SELECT
    e.full_name,
    d.dept_name,
    d.location
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.status = 'active'
ORDER BY d.dept_name, e.full_name;

-- Query 3: Aggregate — headcount and avg salary per department
SELECT
    d.dept_name,
    COUNT(e.emp_id)           AS headcount,
    ROUND(AVG(e.salary), 2)   AS avg_salary,
    MAX(e.salary)             AS max_salary
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
HAVING COUNT(e.emp_id) > 1
ORDER BY avg_salary DESC;

-- Query 4: Subquery — employees earning above company average
SELECT full_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

-- Query 5: CTE — rank employees by salary within each department
WITH ranked AS (
    SELECT
        full_name,
        dept_id,
        salary,
        RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank
    FROM employees
    WHERE status = 'active'
)
SELECT
    r.full_name,
    d.dept_name,
    r.salary,
    r.salary_rank
FROM ranked r
JOIN departments d ON r.dept_id = d.dept_id
WHERE r.salary_rank = 1
ORDER BY d.dept_name;

-- Query 6: Combined JOIN + aggregate + HAVING
SELECT
    d.dept_name,
    d.location,
    COUNT(e.emp_id)     AS total_employees,
    SUM(e.salary)       AS total_payroll
FROM departments d
JOIN employees e ON d.dept_id = e.dept_id
WHERE e.status = 'active'
GROUP BY d.dept_name, d.location
HAVING SUM(e.salary) > 100000
ORDER BY total_payroll DESC;

-- Create roles
CREATE ROLE schema_reader;
CREATE ROLE schema_writer;

-- Grant schema access (required before table-level grants take effect)
GRANT USAGE ON SCHEMA public TO schema_reader;
GRANT USAGE ON SCHEMA public TO schema_writer;

-- Reader: SELECT only on all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO schema_reader;

-- Writer: SELECT + INSERT + UPDATE on all tables
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO schema_writer;

-- Create users and assign roles
CREATE USER readonly_user WITH PASSWORD 'read_pass_123';
GRANT schema_reader TO readonly_user;

CREATE USER app_user WITH PASSWORD 'app_pass_456';
GRANT schema_writer TO app_user;

-- Transaction: transfer an employee to a new department
-- Both the update and the audit log entry must succeed together.
-- If the audit insert fails, the transfer is also rolled back.
BEGIN;

-- Step 1: Snapshot state before any changes
SAVEPOINT before_transfer;

-- Step 2: Move the employee to the Finance department
-- FK is resolved by subquery — no hardcoded ID
UPDATE employees
SET dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'Finance'),
    status  = 'active'
WHERE full_name = 'Alice Johnson';

-- If the UPDATE affected 0 rows (employee not found), roll back:
-- ROLLBACK TO SAVEPOINT before_transfer;

-- Step 3: Record the transfer in an audit log
-- If this fails, the entire transaction including Step 2 is undone
INSERT INTO audit_log (action, performed_at, description)
VALUES (
    'DEPT_TRANSFER',
    NOW(),
    'Alice Johnson transferred to Finance department'
);

-- Both steps committed as one atomic unit
COMMIT;