-- in the practice database (\c practice)
CREATE SCHEMA IF NOT EXISTS constraints_and_keys;
-- start clean, safe to re-run
DROP TABLE IF EXISTS
  constraints_and_keys.users CASCADE;
CREATE TABLE constraints_and_keys.users (
  user_id    SERIAL PRIMARY KEY,
  username   VARCHAR(50) NOT NULL,
  email      VARCHAR(100) NOT NULL
);

-- Valid write: all required columns provided:
INSERT INTO constraints_and_keys.users (username, email) VALUES ('aarav', 'aarav@shop.com'); -- OK!
SELECT * FROM constraints_and_keys.users LIMIT 10;

-- FAILS: username is omitted and has no default:
INSERT INTO constraints_and_keys.users (email) VALUES ('missing@shop.com');
-- 💥 ERROR: null value in column "username" violates not-null constraint
-- DETAIL: Failing row contains (2, null, missing@shop.com).

-- In-Place Schema Evolution on Existing Tables:
-- Step 1: Pre-clean dirty rows (otherwise SET NOT NULL aborts!)
UPDATE constraints_and_keys.users SET username = 'anonymous' WHERE username IS NULL;
-- Step 2: Lock the column against future NULL writes:
ALTER TABLE constraints_and_keys.users ALTER COLUMN username SET NOT NULL;

-- in the practice database (\c practice)
CREATE SCHEMA IF NOT EXISTS constraints_and_keys;
-- start clean, safe to re-run
DROP TABLE IF EXISTS
  constraints_and_keys.web_orders CASCADE;
CREATE TABLE constraints_and_keys.web_orders (
  order_id      SERIAL PRIMARY KEY,
  customer_name VARCHAR(100) NOT NULL,
  order_status  VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Provide ONLY the required column; defaults fill the rest automatically:
INSERT INTO constraints_and_keys.web_orders (customer_name) VALUES ('Priya');
-- order_status='pending', created_at=now() auto-populated!

-- Explicit override is still permitted:
INSERT INTO constraints_and_keys.web_orders (customer_name, order_status)
VALUES ('Aarav', 'shipped'); -- order_status becomes 'shipped'

-- Managing defaults on existing tables:
ALTER TABLE constraints_and_keys.web_orders ALTER COLUMN order_status SET DEFAULT 'placed';
ALTER TABLE constraints_and_keys.web_orders ALTER COLUMN order_status DROP DEFAULT;

-- See what the defaults filled in:
SELECT order_id, customer_name, order_status, created_at FROM constraints_and_keys.web_orders LIMIT 10;

-- in the practice database (\c practice)
CREATE SCHEMA IF NOT EXISTS constraints_and_keys;
-- start clean, safe to re-run
DROP TABLE IF EXISTS
  constraints_and_keys.order_items CASCADE;
CREATE TABLE constraints_and_keys.order_items (
  item_id    SERIAL PRIMARY KEY,
  item_name  VARCHAR(100) NOT NULL,
  unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price > 0),
  quantity   INT NOT NULL CHECK (quantity >= 1),
  status     VARCHAR(20) NOT NULL
    CHECK (status IN ('pending', 'packed', 'dispatched', 'delivered'))
);

INSERT INTO constraints_and_keys.order_items (item_name, unit_price, quantity, status)
VALUES ('Wireless Mouse', 799.00, 2, 'packed'); -- OK

-- Also try (FAILS): status 'in-transit' is not in the approved whitelist
-- INSERT INTO constraints_and_keys.order_items (item_name, unit_price, quantity, status)
-- VALUES ('Keyboard', 1299.00, 1, 'in-transit');
-- ERROR: new row violates check constraint "order_items_status_check"

-- FAILS: price cannot be zero or negative
INSERT INTO constraints_and_keys.order_items (item_name, unit_price, quantity, status)
VALUES ('Bad Item', -50.00, 1, 'packed');
-- ERROR: new row violates check constraint "order_items_unit_price_check"

-- in the practice database (\c practice)
CREATE SCHEMA IF NOT EXISTS constraints_and_keys;
-- 1. Single-column UNIQUE (email must be unique across all users):
-- start clean, safe to re-run
DROP TABLE IF EXISTS
  constraints_and_keys.tax_filings,
  constraints_and_keys.tenant_workspaces,
  constraints_and_keys.accounts CASCADE;
CREATE TABLE constraints_and_keys.accounts (
  account_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      VARCHAR(150) UNIQUE NOT NULL
);

-- 2. Multi-column composite UNIQUE (slug unique within the same tenant):
CREATE TABLE constraints_and_keys.tenant_workspaces (
  workspace_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id    INT NOT NULL,
  slug         VARCHAR(50) NOT NULL,
  CONSTRAINT uq_tenant_slug UNIQUE (tenant_id, slug)
);

-- 3. PostgreSQL 15+ (allows at most ONE null row instead of unlimited NULL duplicates):
CREATE TABLE constraints_and_keys.tax_filings (
  filing_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tax_id     VARCHAR(20) UNIQUE NULLS NOT DISTINCT
);

-- Same slug in two different tenants is fine:
INSERT INTO constraints_and_keys.tenant_workspaces (tenant_id, slug) VALUES (1, 'sales'), (2, 'sales');
SELECT tenant_id, slug FROM constraints_and_keys.tenant_workspaces LIMIT 10;
-- Now try (FAILS with Error 23505): the same slug twice in tenant 1
-- INSERT INTO constraints_and_keys.tenant_workspaces (tenant_id, slug) VALUES (1, 'sales');

-- in the practice database (\c practice)
CREATE SCHEMA IF NOT EXISTS constraints_and_keys;
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- start clean, safe to re-run
DROP TABLE IF EXISTS
  constraints_and_keys.doctor_schedules CASCADE;
CREATE TABLE constraints_and_keys.doctor_schedules (
  booking_id  SERIAL PRIMARY KEY,
  doctor_id   INT NOT NULL,
  slot_window TSRANGE NOT NULL,
  -- For any two rows, if doctor_id is EQUAL (=), slot_window CANNOT OVERLAP (&&):
  CONSTRAINT no_double_booking
    EXCLUDE USING gist (doctor_id WITH =, slot_window WITH &&)
);

-- Doctor 101 booked 10:00 to 11:00
INSERT INTO constraints_and_keys.doctor_schedules (doctor_id, slot_window)
VALUES (101, tsrange('2026-10-10 10:00', '2026-10-10 11:00'));

-- FAILS: 10:30 to 11:30 collides with 10:00 to 11:00!
INSERT INTO constraints_and_keys.doctor_schedules (doctor_id, slot_window)
VALUES (101, tsrange('2026-10-10 10:30', '2026-10-10 11:30'));
-- ERROR: conflicting key value violates exclusion constraint "no_double_booking"
-- DETAIL: Key (doctor_id, slot_window)=(101, ["2026-10-10 10:30:00","2026-10-10 11:30:00"))
-- conflicts with existing key (101, ["2026-10-10 10:00:00","2026-10-10 11:00:00")).