deploy-backend/database/migration_v2.sql

-- =====================================================================
-- ACADFLOW CRM SAAS MULTI-TENANT & SECURITY MIGRATION (V2)
-- =====================================================================
-- Run this script in your Supabase SQL Editor to transition your database 
-- to a production-grade multi-tenant SaaS schema.

BEGIN;

-- 1. Create Tenant Organization Table
CREATE TABLE IF NOT EXISTS organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    plan_type VARCHAR(50) DEFAULT 'Starter', -- e.g., 'Starter', 'Growth', 'Enterprise'
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Create Branch Table
CREATE TABLE IF NOT EXISTS branches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    location VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 3. Insert Default Tenant & Branch to prevent breaking existing data
INSERT INTO organizations (id, name, plan_type) 
VALUES ('00000000-0000-0000-0000-000000000001', 'Default Academy', 'Starter')
ON CONFLICT (id) DO NOTHING;

INSERT INTO branches (id, organization_id, name, location) 
VALUES ('00000000-0000-0000-0000-000000000002', '00000000-0000-0000-0000-000000000001', 'Primary Branch', 'Main Office')
ON CONFLICT (id) DO NOTHING;


-- 4. Alter Counselors Table
ALTER TABLE counselors ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL;
ALTER TABLE counselors ADD COLUMN IF NOT EXISTS branch_id UUID REFERENCES branches(id) ON DELETE SET NULL;
ALTER TABLE counselors ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP WITH TIME ZONE;

-- Update existing counselors to default tenant
UPDATE counselors SET organization_id = '00000000-0000-0000-0000-000000000001' WHERE organization_id IS NULL;
UPDATE counselors SET branch_id = '00000000-0000-0000-0000-000000000002' WHERE branch_id IS NULL;


-- 5. Alter Leads Table
ALTER TABLE leads ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS branch_id UUID REFERENCES branches(id) ON DELETE SET NULL;
ALTER TABLE leads ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP WITH TIME ZONE;

-- Update existing leads to default tenant
UPDATE leads SET organization_id = '00000000-0000-0000-0000-000000000001' WHERE organization_id IS NULL;
UPDATE leads SET branch_id = '00000000-0000-0000-0000-000000000002' WHERE branch_id IS NULL;


-- 6. Alter FollowUps Table
ALTER TABLE follow_ups ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL;
ALTER TABLE follow_ups ADD COLUMN IF NOT EXISTS branch_id UUID REFERENCES branches(id) ON DELETE SET NULL;
ALTER TABLE follow_ups ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP WITH TIME ZONE;

-- Update existing follow-ups to default tenant
UPDATE follow_ups SET organization_id = '00000000-0000-0000-0000-000000000001' WHERE organization_id IS NULL;
UPDATE follow_ups SET branch_id = '00000000-0000-0000-0000-000000000002' WHERE branch_id IS NULL;


-- 7. Alter Admissions Table
ALTER TABLE admissions ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL;
ALTER TABLE admissions ADD COLUMN IF NOT EXISTS branch_id UUID REFERENCES branches(id) ON DELETE SET NULL;
ALTER TABLE admissions ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP WITH TIME ZONE;

-- Update existing admissions to default tenant
UPDATE admissions SET organization_id = '00000000-0000-0000-0000-000000000001' WHERE organization_id IS NULL;
UPDATE admissions SET branch_id = '00000000-0000-0000-0000-000000000002' WHERE branch_id IS NULL;


-- 8. Alter AI Insights Table
ALTER TABLE ai_insights ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id) ON DELETE SET NULL;
ALTER TABLE ai_insights ADD COLUMN IF NOT EXISTS branch_id UUID REFERENCES branches(id) ON DELETE SET NULL;
ALTER TABLE ai_insights ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP WITH TIME ZONE;

-- Update existing insights to default tenant
UPDATE ai_insights SET organization_id = '00000000-0000-0000-0000-000000000001' WHERE organization_id IS NULL;
UPDATE ai_insights SET branch_id = '00000000-0000-0000-0000-000000000002' WHERE branch_id IS NULL;


-- 9. Apply NOT NULL constraints after data updates to keep db strict
ALTER TABLE counselors ALTER COLUMN organization_id SET NOT NULL;
ALTER TABLE leads ALTER COLUMN organization_id SET NOT NULL;
ALTER TABLE follow_ups ALTER COLUMN organization_id SET NOT NULL;
ALTER TABLE admissions ALTER COLUMN organization_id SET NOT NULL;
ALTER TABLE ai_insights ALTER COLUMN organization_id SET NOT NULL;


-- 10. Create Active Views to auto-filter soft-deleted rows in application reads
CREATE OR REPLACE VIEW active_counselors AS 
SELECT * FROM counselors WHERE deleted_at IS NULL;

CREATE OR REPLACE VIEW active_leads AS 
SELECT * FROM leads WHERE deleted_at IS NULL;

CREATE OR REPLACE VIEW active_follow_ups AS 
SELECT * FROM follow_ups WHERE deleted_at IS NULL;

CREATE OR REPLACE VIEW active_admissions AS 
SELECT * FROM admissions WHERE deleted_at IS NULL;

CREATE OR REPLACE VIEW active_ai_insights AS 
SELECT * FROM ai_insights WHERE deleted_at IS NULL;


-- 11. Row-Level Security (RLS) Policies
-- Turn on RLS on tables
ALTER TABLE counselors ENABLE ROW LEVEL SECURITY;
ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
ALTER TABLE follow_ups ENABLE ROW LEVEL SECURITY;
ALTER TABLE admissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE ai_insights ENABLE ROW LEVEL SECURITY;

-- Dynamic Tenant Organization Access Policies (Isolate data strictly by tenant UUID in user metadata JWT claim)
CREATE POLICY org_counselor_isolation ON counselors
    FOR ALL TO authenticated
    USING (organization_id = (auth.jwt() -> 'user_metadata' ->> 'organization_id')::uuid);

CREATE POLICY org_lead_isolation ON leads
    FOR ALL TO authenticated
    USING (organization_id = (auth.jwt() -> 'user_metadata' ->> 'organization_id')::uuid);

CREATE POLICY org_followup_isolation ON follow_ups
    FOR ALL TO authenticated
    USING (organization_id = (auth.jwt() -> 'user_metadata' ->> 'organization_id')::uuid);

CREATE POLICY org_admission_isolation ON admissions
    FOR ALL TO authenticated
    USING (organization_id = (auth.jwt() -> 'user_metadata' ->> 'organization_id')::uuid);

CREATE POLICY org_insight_isolation ON ai_insights
    FOR ALL TO authenticated
    USING (organization_id = (auth.jwt() -> 'user_metadata' ->> 'organization_id')::uuid);

COMMIT;