--POSTGRESQL RANGE TYPES – PRACTICAL EXAMPLES WITH DATA
CREATE TABLE employee_contracts (
    emp_id INT,
    emp_name TEXT,
    period daterange,
    salary NUMERIC
);

INSERT INTO employee_contracts VALUES
(1, 'John Smith',  '[2024-01-01,2024-12-31]', 3000),
(1, 'John Smith',  '[2025-01-01,2025-12-31]', 3200),
(2, 'Mary Adams',  '[2024-06-01,2025-05-31]', 2800),
(3, 'Peter Jones', '[2025-01-01,2025-06-30]', 2500),
(4, 'Alice Brown', 'empty', 0);

SELECT * FROM employee_contracts;

SELECT emp_name, period
FROM employee_contracts
WHERE period @> CURRENT_DATE;

--EXAMPLE – OVERLAPPING INTERVAL SEARCH


SELECT emp_name, period
FROM employee_contracts
WHERE period && '[2025-01-01,2025-03-01]';

SELECT '[2024-01-01,2024-12-31]'::daterange @> '2024-07-01'::date;
--Result: true

--EXAMPLE – CHECK OVERLAP BETWEEN TWO RANGES
SELECT '[2024-01-01,2024-12-31]'::daterange
       && '[2024-06-01,2025-01-01]'::daterange;

SELECT '[2024-01-01,2024-12-31]'::daterange
       -|- '[2025-01-01,2025-12-31]'::daterange;

SELECT lower('[10,20]'::int4range);

SELECT upper('[10,20]'::int4range);

SELECT isempty('empty'::int4range);

SELECT range_merge('[1,5]'::int4range, '[6,10]'::int4range);


CREATE TABLE discount_ranges (
    category TEXT,
    amount numrange
);

INSERT INTO discount_ranges VALUES
('Small', '[0,100)'),
('Medium', '[100,500)'),
('Large', '[500,1000]');


SELECT category
FROM discount_ranges
WHERE amount @> 250.0;


CREATE TABLE meetings (
    meeting_id SERIAL,
    topic TEXT,
    time_slot tsrange
);

INSERT INTO meetings (topic, time_slot) VALUES
('Planning', '[2025-02-01 10:00, 2025-02-01 11:00)'),
('Review',   '[2025-02-01 11:00, 2025-02-01 12:00)'),
('Demo',     '[2025-02-01 14:00, 2025-02-01 15:00)');

SELECT *
FROM meetings
WHERE time_slot && '[2025-02-01 10:30, 2025-02-01 10:45)';


CREATE TABLE subscriptions (
    user_id INT,
    valid_period daterange
);

INSERT INTO subscriptions VALUES
(1, '[2024-01-01,)'),
(2, '(,2025-01-01]'),
(3, '[2024-06-01,2024-12-31]');

SELECT *
FROM subscriptions
WHERE valid_period @> CURRENT_DATE;

--INDEXING RANGE COLUMNS

CREATE INDEX idx_contracts_period
ON employee_contracts
USING gist (period);

--Example Query Using Index
SELECT *
FROM employee_contracts
WHERE period && '[2025-01-01,2025-12-31]';


CREATE INDEX idx_meetings_spgist
ON meetings
USING spgist (time_slot);

SELECT *
FROM employee_contracts
WHERE isempty(period);

--checking overlapping ranges in PostgreSQL
CREATE TABLE reservations (
    id serial,
    room text,
    period daterange
);

INSERT INTO reservations (room, period) VALUES
('A', '[2025-01-01,2025-01-10]'),
('A', '[2025-01-15,2025-01-20]'),
('B', '[2025-01-05,2025-01-12]');

INSERT INTO reservations (room, period) VALUES
('A', '[2025-01-06,2025-01-20]'),
('B', '[2025-01-04,2025-01-20]'),
('C', '[2025-01-05,2025-01-12]');

select * from reservations
--Check Overlap Between Rows in the Same Table
SELECT r1.id AS id1,
       r2.id AS id2,
       r1.room,
       r1.period AS period1,
       r2.period AS period2
FROM reservations r1
JOIN reservations r2
  ON r1.room = r2.room
 AND r1.id < r2.id
 AND r1.period && r2.period;

--Check Overlap Between Rows using range type functionality
SELECT *
FROM reservations
WHERE room = 'A'
AND period && '[2025-01-01,2025-01-16]'