-- ═══════════════════════════════════════════════════════════════
-- SOMANTIX — Rename org_id → organization_id across all tables
-- Migration: 002_rename_org_to_organization.sql
-- Date: April 17, 2026
-- Run this in: Supabase Dashboard → SQL Editor
-- ═══════════════════════════════════════════════════════════════

-- users
ALTER TABLE users RENAME COLUMN org_id TO organization_id;
ALTER TABLE users RENAME COLUMN org_role TO organization_role;

-- projects
ALTER TABLE projects RENAME COLUMN owner_org_id TO owner_organization_id;

-- project_assignments
ALTER TABLE project_assignments RENAME COLUMN org_id TO organization_id;

-- deliveries
ALTER TABLE deliveries RENAME COLUMN org_id TO organization_id;

-- vendor_plans
ALTER TABLE vendor_plans RENAME COLUMN org_id TO organization_id;

-- templates
ALTER TABLE templates RENAME COLUMN org_id TO organization_id;

-- Rename indexes to match new column names
ALTER INDEX idx_users_org_id RENAME TO idx_users_organization_id;
ALTER INDEX idx_projects_owner_org RENAME TO idx_projects_owner_organization;
ALTER INDEX idx_pa_org RENAME TO idx_pa_organization;
ALTER INDEX idx_deliveries_org RENAME TO idx_deliveries_organization;
ALTER INDEX idx_vp_org RENAME TO idx_vp_organization;
ALTER INDEX idx_templates_org RENAME TO idx_templates_organization;

-- Update auto-owner trigger to use new column names
CREATE OR REPLACE FUNCTION auto_create_owner_assignment()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO project_assignments (project_id, organization_id, role)
  VALUES (NEW.id, NEW.owner_organization_id, 'owner');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
