-- Create Database with settings
CREATE DATABASE school_db
  WITH
  OWNER     = postgres
  ENCODING  = 'UTF8'
  LC_COLLATE= 'en_US.UTF-8'
  TEMPLATE  = template0;

-- Schemas (namespaces)
CREATE SCHEMA hr;
CREATE SCHEMA finance;

SET search_path = hr, public;

-- Show search path
SHOW search_path;

CREATE TABLE departments (
  id         INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       VARCHAR(100) NOT NULL UNIQUE,
  budget     NUMERIC(12,2) DEFAULT 0 CHECK (budget >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE employees (
  id          INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name  VARCHAR(60) NOT NULL,
  last_name   VARCHAR(60) NOT NULL,
  email       TEXT UNIQUE NOT NULL
              CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
  salary      NUMERIC(10,2) CHECK (salary > 0),
  gender      CHAR(1) CHECK (gender IN ('M','F','O')),
  dept_id     INT REFERENCES departments(id) ON DELETE SET NULL,
  hired_at    DATE NOT NULL DEFAULT CURRENT_DATE,
  is_active   BOOLEAN NOT NULL DEFAULT TRUE,
  metadata    JSONB DEFAULT '{}',

  -- Table-level constraint
  CONSTRAINT chk_name CHECK (length(trim(first_name)) > 0)
);

-- ALTER TABLE
ALTER TABLE employees
  ADD COLUMN phone      VARCHAR(20),
  ADD COLUMN updated_at TIMESTAMPTZ DEFAULT NOW(),
  ALTER COLUMN salary   SET DEFAULT 1000,
  RENAME COLUMN phone TO phone_number;

-- Comments
COMMENT ON TABLE employees   IS 'Employee master table';
COMMENT ON COLUMN employees.salary IS 'Monthly salary in USD';

-- ENUM Type
CREATE TYPE status_enum AS ENUM ('pending', 'active', 'inactive', 'deleted');
CREATE TYPE priority_t AS ENUM ('low', 'medium', 'high', 'critical');

CREATE TABLE tasks (
  id       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title    TEXT NOT NULL,
  status   status_enum DEFAULT 'pending',
  priority priority_t  DEFAULT 'medium'
);

-- ARRAY Type
CREATE TABLE products (
  id       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT,
  tags     TEXT[],               -- text array
  scores   INTEGER[],            -- int array
  matrix   INTEGER[][]           -- 2D array
);
INSERT INTO products (name, tags) VALUES
  ('Laptop', ARRAY['electronics', 'computer', 'portable']);

-- RANGE Type
CREATE TABLE reservations (
  id       INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room     INT,
  during   TSTZRANGE,              -- timestamp with tz range
  price    NUMRANGE               -- numeric range
);
INSERT INTO reservations (room, during) VALUES
  (101, '[2024-01-10, 2024-01-15)');

-- UUID
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
SELECT gen_random_uuid();  -- PostgreSQL 14+ built-in

-- Network Types
CREATE TABLE servers (
  ip     INET,         -- IP address (v4/v6)
  subnet CIDR,         -- Network address
  mac    MACADDR       -- MAC address
);

-- Basic INSERT
INSERT INTO departments (name, budget)
VALUES ('Engineering', 500000)
RETURNING id, created_at;  -- return generated values

-- Multi-row INSERT
INSERT INTO employees (first_name, last_name, email, salary, dept_id)
VALUES
  ('ដារា',   'សុខ',  'dara@co.com',   2500, 1),
  ('ពិសី',  'ចាន់', 'pisey@co.com',  3000, 1),
  ('វណ្ណ',  'ហេង', 'vann@co.com',   2800, 2)
RETURNING *;

-- UPSERT: INSERT ... ON CONFLICT
INSERT INTO employees (email, first_name, salary)
VALUES ('dara@co.com', 'ដារា', 2700)
ON CONFLICT (email) DO UPDATE SET
  salary     = EXCLUDED.salary,
  updated_at = NOW()
RETURNING *;

-- ON CONFLICT DO NOTHING
INSERT INTO employees (email, first_name)
VALUES ('dara@co.com', 'ដារា')
ON CONFLICT DO NOTHING;

-- COPY (bulk import — very fast)
COPY employees (first_name, last_name, email)
FROM '/path/to/employees.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- SELECT with aliases + computed columns
SELECT
  e.id,
  e.first_name || ' ' || e.last_name  AS full_name,
  e.salary,
  e.salary * 12                        AS annual_salary,
  DATE_PART('year', AGE(e.hired_at))   AS years_employed,
  d.name                                AS department
FROM   employees e
JOIN   departments d ON d.id = e.dept_id
ORDER BY e.salary DESC
LIMIT  10 OFFSET 0;

-- UPDATE with RETURNING
UPDATE employees
SET
  salary     = salary * 1.10,     -- 10% raise
  updated_at = NOW()
WHERE  dept_id = 1
  AND  is_active = TRUE
  AND  DATE_PART('year', AGE(hired_at)) >= 2
RETURNING id, first_name, salary AS new_salary;

-- DELETE RETURNING
DELETE FROM employees
WHERE is_active = FALSE
  AND hired_at < NOW() - INTERVAL '5 years'
RETURNING *;

-- TRUNCATE (fast delete all)
TRUNCATE TABLE employees RESTART IDENTITY CASCADE;

-- Comparison
SELECT * FROM employees WHERE salary > 3000;
SELECT * FROM employees WHERE salary BETWEEN 2000 AND 4000;
SELECT * FROM employees WHERE dept_id IN (1, 2, 3);
SELECT * FROM employees WHERE dept_id IS NULL;
SELECT * FROM employees WHERE is_active = TRUE;

-- LIKE / ILIKE (case-insensitive)
SELECT * FROM employees
WHERE first_name ILIKE '%ដ%';   -- contains ដ (case-insensitive)

-- ~ Regex matching (PostgreSQL special)
SELECT * FROM employees
WHERE email ~ '^[a-z]+@company\.com$';  -- case-sensitive regex

SELECT * FROM employees
WHERE email ~* '^[a-z]+@company\.com$'; -- case-insensitive regex

-- ANY / ALL
SELECT * FROM employees
WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY dept_id);

-- DISTINCT ON (PostgreSQL only)
SELECT DISTINCT ON (dept_id)
  dept_id, first_name, salary
FROM  employees
ORDER BY dept_id, salary DESC;  -- highest paid per dept

-- Array operators
SELECT * FROM products
WHERE 'electronics' = ANY(tags);  -- array contains element

SELECT * FROM products
WHERE tags @> ARRAY['electronics', 'portable']; -- array contains all

-- INNER JOIN — only matching rows
SELECT
  e.first_name, e.last_name,
  d.name AS dept_name
FROM   employees   e
INNER JOIN departments d ON d.id = e.dept_id;

-- LEFT JOIN — all from left + matching right (NULL if no match)
SELECT
  e.first_name,
  d.name AS dept_name,
  COALESCE(d.name, 'No Department') AS dept_or_none
FROM   employees    e
LEFT JOIN departments d ON d.id = e.dept_id;

-- FULL OUTER JOIN
SELECT
  e.first_name,
  d.name
FROM   employees   e
FULL OUTER JOIN departments d ON d.id = e.dept_id;

-- SELF JOIN — employees and their managers
SELECT
  e.first_name  AS employee,
  m.first_name  AS manager
FROM      employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- LATERAL JOIN — correlated subquery per row
SELECT
  d.name AS dept,
  top.first_name,
  top.salary
FROM departments d
CROSS JOIN LATERAL (
  SELECT first_name, salary
  FROM   employees
  WHERE  dept_id = d.id
  ORDER BY salary DESC
  LIMIT 3
) top;

-- Basic Aggregates
SELECT
  d.name                              AS dept,
  COUNT(*)                              AS total_emp,
  COUNT(e.salary)                       AS emp_with_salary,
  ROUND(AVG(e.salary), 2)              AS avg_salary,
  SUM(e.salary)                         AS total_salary,
  MIN(e.salary)                         AS min_salary,
  MAX(e.salary)                         AS max_salary,
  STRING_AGG(e.first_name, ', ' ORDER BY e.first_name) AS names
FROM      departments d
LEFT JOIN employees   e ON e.dept_id = d.id
GROUP BY  d.id, d.name
HAVING    COUNT(*) > 2
ORDER BY  total_salary DESC;

-- Conditional Aggregate with FILTER
SELECT
  COUNT(*) FILTER (WHERE is_active = TRUE)  AS active,
  COUNT(*) FILTER (WHERE is_active = FALSE) AS inactive,
  AVG(salary) FILTER (WHERE gender = 'F')  AS female_avg_salary
FROM employees;

-- Percentile (statistical)
SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees;

-- GROUPING SETS (multiple GROUP BY at once)
SELECT dept_id, gender, COUNT(*), AVG(salary)
FROM  employees
GROUP BY GROUPING SETS (
  (dept_id, gender),
  (dept_id),
  (gender),
  ()
);

-- ROLLUP (subtotals + grand total)
SELECT dept_id, gender, SUM(salary)
FROM  employees
GROUP BY ROLLUP (dept_id, gender);

-- Basic CTE
WITH
  dept_stats AS (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept_id
  ),
  top_earners AS (
    SELECT e.*
    FROM  employees e
    JOIN  dept_stats ds ON ds.dept_id = e.dept_id
    WHERE e.salary > ds.avg_sal
  )
SELECT * FROM top_earners;

-- Recursive CTE — org chart / hierarchy
WITH RECURSIVE org_tree AS (
  -- Anchor: top-level (no manager)
  SELECT id, first_name, manager_id, 0 AS depth
  FROM  employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: employees under previous level
  SELECT e.id, e.first_name, e.manager_id, t.depth + 1
  FROM  employees e
  INNER JOIN org_tree t ON t.id = e.manager_id
)
SELECT
  REPEAT('  ', depth) || first_name AS name_tree,
  depth
FROM org_tree
ORDER BY depth;

-- Writeable CTE (INSERT/UPDATE/DELETE in CTE)
WITH
  moved_emp AS (
    DELETE FROM employees
    WHERE dept_id = 5
    RETURNING *
  )
INSERT INTO archive_employees
SELECT * FROM moved_emp;

-- Regular View
CREATE OR REPLACE VIEW v_employee_details AS
SELECT
  e.id,
  e.first_name || ' ' || e.last_name AS full_name,
  e.email,
  e.salary,
  d.name                              AS dept_name,
  EXTRACT(YEAR FROM AGE(e.hired_at))  AS years_exp
FROM      employees   e
LEFT JOIN departments d ON d.id = e.dept_id
WHERE e.is_active = TRUE;

-- Updatable View with CHECK OPTION
CREATE VIEW v_active_employees AS
SELECT * FROM employees
WHERE is_active = TRUE
WITH CHECK OPTION;  -- prevent inserting inactive through view

-- MATERIALIZED VIEW (cached query result)
CREATE MATERIALIZED VIEW mv_dept_summary
WITH (fillfactor = 90)
AS
SELECT
  d.id,
  d.name,
  COUNT(e.id)            AS emp_count,
  ROUND(AVG(e.salary),2) AS avg_salary,
  NOW()                    AS last_refreshed
FROM      departments d
LEFT JOIN employees   e ON e.dept_id = d.id
GROUP BY  d.id, d.name
WITH DATA;  -- immediately populate

-- Create unique index for concurrent refresh
CREATE UNIQUE INDEX ON mv_dept_summary(id);

-- Refresh (non-blocking)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dept_summary;

-- Query it like a table
SELECT * FROM mv_dept_summary ORDER BY emp_count DESC;

SELECT
  first_name,
  dept_id,
  salary,

  -- Ranking Functions
  ROW_NUMBER()   OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num,
  RANK()         OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank,
  DENSE_RANK()   OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank,
  PERCENT_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS pct_rank,
  NTILE(4)       OVER (ORDER BY salary) AS quartile,

  -- Aggregate as Window
  SUM(salary)  OVER (PARTITION BY dept_id) AS dept_total,
  AVG(salary)  OVER (PARTITION BY dept_id) AS dept_avg,
  COUNT(*)      OVER (PARTITION BY dept_id) AS dept_size,
  salary / SUM(salary) OVER (PARTITION BY dept_id) * 100 AS salary_pct,

  -- Lead/Lag (previous/next row)
  LAG(salary, 1, 0)  OVER (ORDER BY hired_at) AS prev_salary,
  LEAD(salary, 1, 0) OVER (ORDER BY hired_at) AS next_salary,

  -- First/Last value in partition
  FIRST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS highest_in_dept,

  -- Running total (Frame clause)
  SUM(salary) OVER (
    PARTITION BY dept_id
    ORDER BY     hired_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,

  -- Moving average (3-row window)
  AVG(salary) OVER (
    ORDER BY hired_at
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3

FROM employees
ORDER BY dept_id, salary DESC;

-- B-tree (default) — equality & range
CREATE INDEX idx_emp_email  ON employees(email);
CREATE INDEX idx_emp_salary ON employees(salary DESC);

-- Composite Index
CREATE INDEX idx_emp_dept_sal ON employees(dept_id, salary DESC);

-- Partial Index (index only active employees)
CREATE INDEX idx_emp_active ON employees(email)
WHERE is_active = TRUE;

-- Expression Index
CREATE INDEX idx_emp_lower_email ON employees(lower(email));

-- Hash Index (only equality =)
CREATE INDEX idx_emp_hash_id USING hash ON employees(id);

-- GIN Index (Arrays, JSONB, Full-text)
CREATE INDEX idx_products_tags USING gin ON products(tags);
CREATE INDEX idx_emp_metadata USING gin ON employees(metadata jsonb_path_ops);

-- GiST Index (Ranges, Geometric, Full-text)
CREATE INDEX idx_rsv_during USING gist ON reservations(during);

-- BRIN (Block Range) — huge tables with natural ordering
CREATE INDEX idx_log_created USING brin ON logs(created_at);

-- EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM employees
WHERE dept_id = 1 AND salary > 2500;

-- Index usage stats
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM  pg_stat_user_indexes
WHERE relname = 'employees'
ORDER BY idx_scan DESC;

-- Basic Transaction
BEGIN;
  UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
  UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;

-- SAVEPOINT
BEGIN;
  INSERT INTO orders (customer_id, total) VALUES (1, 500);
  SAVEPOINT after_order;

  INSERT INTO payments (order_id, amount) VALUES (lastval(), 500);
  -- If payment fails:
  ROLLBACK TO SAVEPOINT after_order;
  -- Try again...
COMMIT;

-- Isolation Levels
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- default
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Row-Level Locking
SELECT * FROM products
WHERE id = 42
FOR UPDATE;                -- exclusive lock

SELECT * FROM products
WHERE id = 42
FOR SHARE;                 -- shared lock

SELECT * FROM products
WHERE id = 42
FOR UPDATE SKIP LOCKED;    -- skip if locked (queue pattern)

-- Check current locks
SELECT
  pg_stat_activity.pid,
  pg_stat_activity.query,
  pg_locks.mode,
  pg_locks.granted
FROM pg_locks
JOIN pg_stat_activity ON pg_stat_activity.pid = pg_locks.pid
WHERE NOT granted;

-- Function returning scalar
CREATE OR REPLACE FUNCTION get_dept_avg_salary(p_dept_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql STABLE
AS $$
DECLARE
  v_avg NUMERIC;
BEGIN
  SELECT AVG(salary) INTO v_avg
  FROM  employees
  WHERE dept_id = p_dept_id
    AND is_active = TRUE;

  RETURN COALESCE(v_avg, 0);
END;
$$;

-- Function returning TABLE (set-returning)
CREATE OR REPLACE FUNCTION get_top_employees(
  p_dept_id  INT,
  p_limit    INT DEFAULT 5
)
RETURNS TABLE (id INT, full_name TEXT, salary NUMERIC)
LANGUAGE plpgsql STABLE SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT
    e.id,
    e.first_name || ' ' || e.last_name,
    e.salary
  FROM     employees e
  WHERE    e.dept_id = p_dept_id
  ORDER BY e.salary DESC
  LIMIT    p_limit;
END;
$$;

SELECT * FROM get_top_employees(1, 3);

-- Stored PROCEDURE (can commit/rollback)
CREATE OR REPLACE PROCEDURE transfer_salary(
  p_from_id INT,
  p_to_id   INT,
  p_amount  NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE employees SET salary = salary - p_amount WHERE id = p_from_id;
  UPDATE employees SET salary = salary + p_amount WHERE id = p_to_id;

  RAISE NOTICE 'Transferred % from emp % to emp %', p_amount, p_from_id, p_to_id;
EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION 'Transfer failed: %', SQLERRM;
END;
$$;

CALL transfer_salary(1, 2, 200);

-- Audit Log Table
CREATE TABLE audit_log (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name  TEXT,
  operation   TEXT,
  old_data    JSONB,
  new_data    JSONB,
  changed_by  TEXT DEFAULT current_user,
  changed_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Trigger Function
CREATE OR REPLACE FUNCTION fn_audit_trigger()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::jsonb END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::jsonb END
  );
  RETURN NEW;
END;
$$;

-- Attach Trigger to Table
CREATE TRIGGER trg_employees_audit
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION fn_audit_trigger();

-- BEFORE trigger — modify data before insert
CREATE OR REPLACE FUNCTION fn_normalize_email()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN
  NEW.email      = LOWER(TRIM(NEW.email));
  NEW.first_name  = INITCAP(TRIM(NEW.first_name));
  NEW.updated_at  = NOW();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_normalize_emp
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW EXECUTE FUNCTION fn_normalize_email();

-- Insert JSONB data
UPDATE employees SET metadata = '{
  "skills": ["PostgreSQL", "Python", "Docker"],
  "address": {"city": "ភ្នំពេញ", "country": "KH"},
  "score": 95,
  "verified": true
}' WHERE id = 1;

-- JSONB Operators
SELECT
  metadata ->  'address'          AS address_json,   -- JSON object
  metadata ->> 'score'            AS score_text,     -- TEXT
  metadata #>  '{address,city}'   AS city_json,      -- nested JSON
  metadata #>> '{address,city}'   AS city_text,      -- nested TEXT
  (metadata ->> 'score')::INT   AS score_int       -- cast
FROM employees WHERE id = 1;

-- JSONB containment & key existence
SELECT * FROM employees
WHERE metadata @> '{"verified": true}';           -- contains

SELECT * FROM employees
WHERE metadata ? 'skills';                          -- key exists

SELECT * FROM employees
WHERE metadata ?| ARRAY['skills', 'score'];          -- any key exists

-- JSON Path (PostgreSQL 12+)
SELECT jsonb_path_query(metadata, '$.skills[*]')
FROM   employees WHERE id = 1;

-- Modify JSONB
UPDATE employees
SET metadata = jsonb_set(metadata, '{score}', '98')
WHERE id = 1;

UPDATE employees
SET metadata = metadata || '{"department": "Engineering"}'  -- merge
WHERE id = 1;

UPDATE employees
SET metadata = metadata - 'verified'   -- remove key
WHERE id = 1;

-- Full-Text Search
SELECT first_name, last_name,
  ts_rank(to_tsvector('english', first_name || ' ' || last_name),
          plainto_tsquery('english', 'dara sok')) AS rank
FROM employees
WHERE to_tsvector('english', first_name || ' ' || last_name)
      @@ plainto_tsquery('english', 'dara')
ORDER BY rank DESC;

-- Create Roles
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;
CREATE ROLE app_admin;

CREATE USER api_user   WITH PASSWORD 'secure_pass' IN ROLE app_readwrite;
CREATE USER report_userWITH PASSWORD 'secure_pass' IN ROLE app_readonly;

-- GRANT privileges
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO app_readonly;

-- Row Level Security (RLS)
-- Each user sees only their own data
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see their own dept
CREATE POLICY pol_emp_dept ON employees
  FOR ALL
  TO app_readwrite
  USING (
    dept_id = (SELECT dept_id FROM users_mapping
                WHERE username = current_user)
  );

-- Bypass RLS (admin)
ALTER ROLE app_admin BYPASSRLS;

-- Table Partitioning
CREATE TABLE logs (
  id         BIGINT GENERATED ALWAYS AS IDENTITY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  level      TEXT,
  message    TEXT
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2024_01 PARTITION OF logs
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Useful Extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";       -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";        -- Encryption
CREATE EXTENSION IF NOT EXISTS "pg_trgm";         -- Trigram similarity
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements";-- Query statistics
CREATE EXTENSION IF NOT EXISTS "postgis";          -- Geospatial
CREATE EXTENSION IF NOT EXISTS "timescaledb";      -- Time-series