-- schema_patch_v1.1.sql — Security hardening additions
-- Run ONCE after upgrading to v1.1. Safe to run multiple times (idempotent).
-- Execute as the DB superuser or an account with ALTER/CREATE TABLE privileges.

BEGIN;

-- ── 1. Add `active` column to users (soft-delete support) ──────────────────
ALTER TABLE users ADD COLUMN IF NOT EXISTS active BOOLEAN NOT NULL DEFAULT true;
-- Ensure all existing users are active
UPDATE users SET active = true WHERE active IS NULL;

-- ── 2. Index for session/auth lookups ──────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_users_initials_active ON users (initials, active);
CREATE INDEX IF NOT EXISTS idx_users_id_active       ON users (id, active);

-- ── 3. Least-privilege: create read and write roles if they don't exist ────
-- These are OPTIONAL — skip if your hosting plan doesn't support multiple roles.
-- You'll need to connect as a superuser to run these.

DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'apothecary_ro') THEN
    CREATE ROLE apothecary_ro NOLOGIN;
  END IF;
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'apothecary_rw') THEN
    CREATE ROLE apothecary_rw NOLOGIN;
  END IF;
END
$$;

-- Grant read-only access to the RO role
GRANT SELECT ON ALL TABLES IN SCHEMA public TO apothecary_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO apothecary_ro;

-- Grant read-write access to the RW role (what the app uses)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA public TO apothecary_rw;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO apothecary_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES    TO apothecary_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO apothecary_rw;

-- ── 4. Revoke PUBLIC schema access (least privilege) ─────────────────────
-- Prevents any role from accessing tables by default.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- ── 5. Set statement_timeout for the app user ─────────────────────────────
-- Kills runaway queries. Replace 'apothecary_user' with your actual DB username.
-- ALTER ROLE apothecary_user SET statement_timeout = '30s';

-- ── 6. pg_stat_statements for query monitoring (optional) ─────────────────
-- CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

COMMIT;

-- Post-run verification
SELECT column_name, data_type, column_default
FROM information_schema.columns
WHERE table_name = 'users' AND column_name = 'active';

-- v1.2: Link journal entries to recipes
ALTER TABLE journal ADD COLUMN IF NOT EXISTS recipe_id INTEGER REFERENCES recipes(id) ON DELETE SET NULL;