-- Migration 095: Fix subsidiary relationships and add missing companies
-- Based on Magdalena's Excel file: subsidiaries listed below parent on blue rows

-- === FIX: Pelmar parent TTM, not P&P ===
UPDATE companies SET parent_company_id = 92 WHERE id = 51;  -- Pelmar → TTM

-- === NEW SUBSIDIARIES of TTM (id=92) ===
-- ARD Invest (id=19) → TTM
UPDATE companies SET parent_company_id = 92, fee_included_in_parent = TRUE WHERE id = 19;

-- Progress Optima (id=57) → TTM
UPDATE companies SET parent_company_id = 92, fee_included_in_parent = TRUE WHERE id = 57;

-- === N33 / STUDIO N°33 (id=127) → TERMO (id=102) ===
UPDATE companies SET parent_company_id = 102, fee_included_in_parent = TRUE WHERE id = 127;

-- === Fiume Studio (id=128) → Pacific Sun (created below) ===
-- Pacific Sun pays, Fiume is subsidiary

-- === ADD MISSING COMPANIES ===

-- Pacific Sun (parent of Fiume Studio)
INSERT INTO companies (name, slug, status, membership_status, data_quality)
VALUES ('Pacific Sun', 'pacific-sun', 'active', 'active', 'basic')
ON CONFLICT (slug) DO NOTHING;

-- Ultramare
INSERT INTO companies (name, slug, status, membership_status, data_quality)
VALUES ('Ultramare', 'ultramare', 'active', 'active', 'basic')
ON CONFLICT (slug) DO NOTHING;

-- Coach 4 You (Iwona Spaleniak - already exists as id=130, rename)
-- "COACH 4 YOU - IWONA SPALENIAK" from Magdalena's email
-- Company 130 = "Iwona Spaleniak - Doradztwo Rozwoju Osobistego"
-- This is the same person/company, update name to match Excel
UPDATE companies SET name = 'Coach 4 You' WHERE id = 130;

-- IBET
INSERT INTO companies (name, slug, status, membership_status, data_quality)
VALUES ('IBET', 'ibet', 'active', 'active', 'basic')
ON CONFLICT (slug) DO NOTHING;

-- Prospoland
INSERT INTO companies (name, slug, status, membership_status, data_quality)
VALUES ('Prospoland', 'prospoland', 'active', 'active', 'basic')
ON CONFLICT (slug) DO NOTHING;

-- Ekonsul
INSERT INTO companies (name, slug, status, membership_status, data_quality)
VALUES ('Ekonsul', 'ekonsul', 'active', 'active', 'basic')
ON CONFLICT (slug) DO NOTHING;

-- Steamset
INSERT INTO companies (name, slug, status, membership_status, data_quality)
VALUES ('Steamset', 'steamset', 'active', 'active', 'basic')
ON CONFLICT (slug) DO NOTHING;

-- Goodwill
INSERT INTO companies (name, slug, status, membership_status, data_quality)
VALUES ('Goodwill', 'goodwill', 'active', 'active', 'basic')
ON CONFLICT (slug) DO NOTHING;

-- Orlex Invest (subsidiary - parent TBD, for now just create)
INSERT INTO companies (name, slug, status, membership_status, fee_included_in_parent, data_quality)
VALUES ('Orlex Invest', 'orlex-invest', 'active', 'active', TRUE, 'basic')
ON CONFLICT (slug) DO NOTHING;

-- === SET Fiume Studio parent to Pacific Sun ===
-- (must run after Pacific Sun insert)
UPDATE companies SET parent_company_id = (SELECT id FROM companies WHERE slug = 'pacific-sun'), fee_included_in_parent = TRUE WHERE id = 128;

-- === Alter Energy (id=129) also under Pacific Sun ===
UPDATE companies SET parent_company_id = (SELECT id FROM companies WHERE slug = 'pacific-sun') WHERE id = 129;

-- === Radio Norda FM (id=91) — already has parent=TTM from before, set fee flag ===
UPDATE companies SET fee_included_in_parent = TRUE WHERE id = 91;

-- === Nadmorski24.pl (id=90) — subsidiary of TTM, set fee flag ===
UPDATE companies SET fee_included_in_parent = TRUE WHERE id = 90;

-- Grant permissions
GRANT ALL ON TABLE companies TO nordabiz_app;