-- Migration: Remove customers table, move everything to user_id on profiles
-- BACKUP YOUR DATABASE BEFORE RUNNING THIS!
-- Run with: psql -d your_db -f config/migrate_remove_customers.sql

BEGIN;

-- ============================================================
-- 1. Add new columns to profiles (from customers)
-- ============================================================

ALTER TABLE profiles
    ADD COLUMN IF NOT EXISTS linkedin_url TEXT,
    ADD COLUMN IF NOT EXISTS writing_style_notes TEXT,
    ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{}'::JSONB,
    ADD COLUMN IF NOT EXISTS profile_picture TEXT,
    ADD COLUMN IF NOT EXISTS creator_email TEXT,
    ADD COLUMN IF NOT EXISTS customer_email TEXT,
    ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT TRUE;

-- ============================================================
-- 2. Migrate data from customers to profiles
-- ============================================================

UPDATE profiles
SET
    linkedin_url        = c.linkedin_url,
    writing_style_notes = c.writing_style_notes,
    metadata            = c.metadata,
    profile_picture     = c.profile_picture,
    creator_email       = c.creator_email,
    customer_email      = c.customer_email,
    is_active           = c.is_active
FROM customers c
WHERE profiles.customer_id = c.id;

-- Also populate display_name from customer.name where profiles.display_name is null
UPDATE profiles
SET display_name = c.name
FROM customers c
WHERE profiles.customer_id = c.id
  AND profiles.display_name IS NULL;

-- ============================================================
-- 3. Add user_id column to all content tables
-- ============================================================

ALTER TABLE linkedin_profiles
    ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;

ALTER TABLE linkedin_posts
    ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;

ALTER TABLE topics
    ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;

ALTER TABLE post_types
    ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;

ALTER TABLE profile_analyses
    ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;

ALTER TABLE research_results
    ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;

ALTER TABLE generated_posts
    ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;

ALTER TABLE example_posts
    ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;

ALTER TABLE reference_profiles
    ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE;

-- ============================================================
-- 4. Populate user_id via profiles.customer_id mapping
-- ============================================================

UPDATE linkedin_profiles lp
SET user_id = p.id
FROM profiles p
WHERE lp.customer_id = p.customer_id
  AND p.customer_id IS NOT NULL;

UPDATE linkedin_posts lp
SET user_id = p.id
FROM profiles p
WHERE lp.customer_id = p.customer_id
  AND p.customer_id IS NOT NULL;

UPDATE topics t
SET user_id = p.id
FROM profiles p
WHERE t.customer_id = p.customer_id
  AND p.customer_id IS NOT NULL;

UPDATE post_types pt
SET user_id = p.id
FROM profiles p
WHERE pt.customer_id = p.customer_id
  AND p.customer_id IS NOT NULL;

UPDATE profile_analyses pa
SET user_id = p.id
FROM profiles p
WHERE pa.customer_id = p.customer_id
  AND p.customer_id IS NOT NULL;

UPDATE research_results rr
SET user_id = p.id
FROM profiles p
WHERE rr.customer_id = p.customer_id
  AND p.customer_id IS NOT NULL;

UPDATE generated_posts gp
SET user_id = p.id
FROM profiles p
WHERE gp.customer_id = p.customer_id
  AND p.customer_id IS NOT NULL;

UPDATE example_posts ep
SET user_id = p.id
FROM profiles p
WHERE ep.customer_id = p.customer_id
  AND p.customer_id IS NOT NULL;

UPDATE reference_profiles rp
SET user_id = p.id
FROM profiles p
WHERE rp.customer_id = p.customer_id
  AND p.customer_id IS NOT NULL;

-- ============================================================
-- 5. Drop old unique constraints (customer_id based)
-- ============================================================

ALTER TABLE linkedin_profiles DROP CONSTRAINT IF EXISTS linkedin_profiles_customer_id_key;
ALTER TABLE profile_analyses DROP CONSTRAINT IF EXISTS profile_analyses_customer_id_key;
ALTER TABLE post_types DROP CONSTRAINT IF EXISTS post_types_customer_id_name_key;
ALTER TABLE linkedin_posts DROP CONSTRAINT IF EXISTS linkedin_posts_customer_id_post_url_key;
ALTER TABLE reference_profiles DROP CONSTRAINT IF EXISTS reference_profiles_customer_id_linkedin_url_key;

-- ============================================================
-- 6. Set user_id NOT NULL (after data migration)
-- ============================================================

-- Delete orphan rows that have no user_id (data without a profile link)
DELETE FROM linkedin_profiles WHERE user_id IS NULL;
DELETE FROM linkedin_posts WHERE user_id IS NULL;
DELETE FROM topics WHERE user_id IS NULL;
DELETE FROM post_types WHERE user_id IS NULL;
DELETE FROM profile_analyses WHERE user_id IS NULL;
DELETE FROM research_results WHERE user_id IS NULL;
DELETE FROM generated_posts WHERE user_id IS NULL;
DELETE FROM example_posts WHERE user_id IS NULL;
DELETE FROM reference_profiles WHERE user_id IS NULL;

ALTER TABLE linkedin_profiles ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE linkedin_posts ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE topics ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE post_types ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE profile_analyses ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE research_results ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE generated_posts ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE example_posts ALTER COLUMN user_id SET NOT NULL;
ALTER TABLE reference_profiles ALTER COLUMN user_id SET NOT NULL;

-- ============================================================
-- 7. Add new unique constraints (user_id based)
-- ============================================================

ALTER TABLE linkedin_profiles ADD CONSTRAINT linkedin_profiles_user_id_key UNIQUE (user_id);
ALTER TABLE profile_analyses ADD CONSTRAINT profile_analyses_user_id_key UNIQUE (user_id);
ALTER TABLE post_types ADD CONSTRAINT post_types_user_id_name_key UNIQUE (user_id, name);
ALTER TABLE linkedin_posts ADD CONSTRAINT linkedin_posts_user_id_post_url_key UNIQUE (user_id, post_url);
ALTER TABLE reference_profiles ADD CONSTRAINT reference_profiles_user_id_linkedin_url_key UNIQUE (user_id, linkedin_url);

-- ============================================================
-- 8. Drop RLS policies that reference customer_id
-- ============================================================

-- Drop ALL policies on generated_posts (we'll recreate them with user_id)
DROP POLICY IF EXISTS "Users can manage posts of their customers" ON generated_posts;
DROP POLICY IF EXISTS "Company members can view company posts" ON generated_posts;
DROP POLICY IF EXISTS "Service role has full access to generated_posts" ON generated_posts;

-- Drop policies on customers table (will be dropped anyway)
DROP POLICY IF EXISTS "Users can manage own customers" ON customers;
DROP POLICY IF EXISTS "Company members can view company customers" ON customers;
DROP POLICY IF EXISTS "Service role has full access to customers" ON customers;

-- ============================================================
-- 8b. Drop users view (depends on profiles.customer_id)
-- ============================================================

DROP VIEW IF EXISTS users;

-- ============================================================
-- 9. Drop old customer_id columns from content tables
-- ============================================================

ALTER TABLE linkedin_profiles DROP COLUMN IF EXISTS customer_id;
ALTER TABLE linkedin_posts DROP COLUMN IF EXISTS customer_id;
ALTER TABLE topics DROP COLUMN IF EXISTS customer_id;
ALTER TABLE post_types DROP COLUMN IF EXISTS customer_id;
ALTER TABLE profile_analyses DROP COLUMN IF EXISTS customer_id;
ALTER TABLE research_results DROP COLUMN IF EXISTS customer_id;
ALTER TABLE generated_posts DROP COLUMN IF EXISTS customer_id;
ALTER TABLE example_posts DROP COLUMN IF EXISTS customer_id;
ALTER TABLE reference_profiles DROP COLUMN IF EXISTS customer_id;

-- Drop customer_id from profiles
ALTER TABLE profiles DROP COLUMN IF EXISTS customer_id;

-- Drop customer_id from api_usage_logs
ALTER TABLE api_usage_logs DROP COLUMN IF EXISTS customer_id;

-- ============================================================
-- 10. Create new RLS policies using user_id
-- ============================================================

-- Generated posts policies (using user_id instead of customer_id)
CREATE POLICY "Users can manage own posts"
    ON generated_posts FOR ALL
    USING (user_id = auth.uid());

CREATE POLICY "Company members can view company posts"
    ON generated_posts FOR SELECT
    USING (
        EXISTS (
            SELECT 1 FROM profiles p1
            JOIN profiles p2 ON p1.company_id = p2.company_id
            WHERE p1.id = auth.uid()
            AND p2.id = generated_posts.user_id
            AND p1.company_id IS NOT NULL
        )
    );

CREATE POLICY "Service role has full access to generated_posts"
    ON generated_posts FOR ALL
    USING (auth.jwt() ->> 'role' = 'service_role');

-- ============================================================
-- 11. Drop old indexes and create new ones
-- ============================================================

DROP INDEX IF EXISTS idx_linkedin_profiles_customer_id;
DROP INDEX IF EXISTS idx_linkedin_posts_customer_id;
DROP INDEX IF EXISTS idx_topics_customer_id;
DROP INDEX IF EXISTS idx_post_types_customer_id;
DROP INDEX IF EXISTS idx_profile_analyses_customer_id;
DROP INDEX IF EXISTS idx_research_results_customer_id;
DROP INDEX IF EXISTS idx_generated_posts_customer_id;
DROP INDEX IF EXISTS idx_example_posts_customer_id;
DROP INDEX IF EXISTS idx_reference_profiles_customer_id;
DROP INDEX IF EXISTS idx_profiles_customer_id;
DROP INDEX IF EXISTS idx_customers_linkedin_url;
DROP INDEX IF EXISTS idx_customers_user_id;
DROP INDEX IF EXISTS idx_customers_company_id;
DROP INDEX IF EXISTS idx_api_usage_logs_customer_id;

CREATE INDEX IF NOT EXISTS idx_linkedin_profiles_user_id ON linkedin_profiles(user_id);
CREATE INDEX IF NOT EXISTS idx_linkedin_posts_user_id ON linkedin_posts(user_id);
CREATE INDEX IF NOT EXISTS idx_topics_user_id ON topics(user_id);
CREATE INDEX IF NOT EXISTS idx_post_types_user_id ON post_types(user_id);
CREATE INDEX IF NOT EXISTS idx_profile_analyses_user_id ON profile_analyses(user_id);
CREATE INDEX IF NOT EXISTS idx_research_results_user_id ON research_results(user_id);
CREATE INDEX IF NOT EXISTS idx_generated_posts_user_id ON generated_posts(user_id);
CREATE INDEX IF NOT EXISTS idx_example_posts_user_id ON example_posts(user_id);
CREATE INDEX IF NOT EXISTS idx_reference_profiles_user_id ON reference_profiles(user_id);

-- ============================================================
-- 12. Drop customers table
-- ============================================================

-- Remove trigger first
DROP TRIGGER IF EXISTS update_customers_updated_at ON customers;

DROP TABLE IF EXISTS customers CASCADE;

-- ============================================================
-- 13. Recreate users view (without customer_id)
-- ============================================================

CREATE OR REPLACE VIEW users AS
SELECT
    au.id,
    au.created_at,
    au.updated_at,
    au.email,
    au.raw_user_meta_data->>'sub' AS linkedin_sub,
    au.raw_user_meta_data->>'vanityName' AS linkedin_vanity_name,
    au.raw_user_meta_data->>'name' AS linkedin_name,
    au.raw_user_meta_data->>'picture' AS linkedin_picture,
    CASE
        WHEN au.raw_user_meta_data->>'iss' LIKE '%linkedin%' THEN 'linkedin_oauth'
        ELSE 'email_password'
    END AS auth_method,
    COALESCE(p.account_type, 'ghostwriter') AS account_type,
    p.display_name,
    COALESCE(p.onboarding_status, 'pending') AS onboarding_status,
    COALESCE(p.onboarding_data, '{}'::jsonb) AS onboarding_data,
    p.company_id,
    p.linkedin_url,
    p.writing_style_notes,
    p.metadata,
    p.profile_picture,
    p.creator_email,
    p.customer_email,
    p.is_active,
    au.email_confirmed_at IS NOT NULL AS email_verified
FROM auth.users au
LEFT JOIN profiles p ON au.id = p.id;

COMMIT;