-- Enterprise Normalized Schema Definition (3NF / BCNF)
CREATE TABLE organizations (
    org_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_name VARCHAR(128) NOT NULL,
    plan_tier VARCHAR(32) NOT NULL DEFAULT 'FREE',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id UUID NOT NULL REFERENCES organizations(org_id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(128) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Foreign Key Indexing: Critical for join performance
CREATE INDEX idx_users_org_id ON users(org_id);

-- Month-Over-Month Revenue Growth with LAG() and DENSE_RANK()
SELECT
    DATE_TRUNC('month', order_date) AS sales_month,
    SUM(total_amount) AS current_month_revenue,
    LAG(SUM(total_amount), 1) OVER (
        ORDER BY DATE_TRUNC('month', order_date)
    ) AS previous_month_revenue,
    ROUND(
        (SUM(total_amount) - LAG(SUM(total_amount), 1) OVER (ORDER BY DATE_TRUNC('month', order_date)))
        / NULLIF(LAG(SUM(total_amount), 1) OVER (ORDER BY DATE_TRUNC('month', order_date)), 0) * 100, 
        2
    ) AS mom_growth_percentage,
    DENSE_RANK() OVER (
        ORDER BY SUM(total_amount) DESC
    ) AS all_time_rank
FROM orders
WHERE order_status = 'COMPLETED'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY sales_month;

-- Recursive Organizational Tree Traversal
WITH RECURSIVE org_hierarchy AS (
    -- Anchor Member: Select top-level CEO (manager_id is NULL)
    SELECT emp_id, full_name, manager_id, 1 AS depth, ARRAY[emp_id] AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive Member: Join employees reporting to previous level
    SELECT e.emp_id, e.full_name, e.manager_id, oh.depth + 1, oh.path || e.emp_id
    FROM employees e
    INNER JOIN org_hierarchy oh ON e.manager_id = oh.emp_id
)
SELECT depth, full_name, path
FROM org_hierarchy
ORDER BY path;

-- GIN Index for JSONB Document Search
CREATE INDEX idx_audit_logs_payload_gin ON audit_logs USING gin (payload_json jsonb_path_ops);

-- BRIN Index for Terabyte Time-Series Logs (100x smaller memory footprint than B-Tree!)
CREATE INDEX idx_sensor_telemetry_created_at_brin ON sensor_telemetry USING brin (created_at);

-- Partial Covering Index for Active User Queries
CREATE INDEX idx_users_active_lookup ON users (email) INCLUDE (full_name, org_id) WHERE is_active = TRUE;

-- Pessimistic Concurrency Control with Row Locking
BEGIN;
SELECT balance 
FROM accounts 
WHERE account_id = 'acc_1001' 
FOR UPDATE; -- Locks the specific row against concurrent modifications

UPDATE accounts 
SET balance = balance - 250 
WHERE account_id = 'acc_1001';

COMMIT;

-- Hardware-Enforced Multi-Tenant Data Isolation with RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy ON documents
    FOR ALL
    USING (org_id = current_setting('app.current_org_id')::UUID);