-- =====================================================
-- Migration 003: Multi-Tenancy Enhancement
-- =====================================================

-- Utöka boc_tenants med koncernstöd
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS org_number VARCHAR(20);
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS vat_number VARCHAR(20);
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS address TEXT;
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS phone VARCHAR(20);
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS email VARCHAR(255);
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS currency VARCHAR(3) DEFAULT 'SEK';
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS language VARCHAR(5) DEFAULT 'sv';
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS timezone VARCHAR(50) DEFAULT 'Europe/Stockholm';
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS fiscal_year_start DATE DEFAULT '2026-01-01';
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS parent_id UUID REFERENCES boc_tenants(id);
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS ledger_db VARCHAR(255);
ALTER TABLE boc_tenants ADD COLUMN IF NOT EXISTS settings JSONB DEFAULT '{}';

-- Skapa index för snabb lookup
CREATE INDEX IF NOT EXISTS idx_tenants_slug ON boc_tenants(slug);
CREATE INDEX IF NOT EXISTS idx_tenants_parent ON boc_tenants(parent_id);
CREATE INDEX IF NOT EXISTS idx_tenants_status ON boc_tenants(status);

-- Tenant-avgränsning på alla tabeller
-- Lägg till tenant_id där det saknas
DO $$
BEGIN
    -- Kolla vilka tabeller som saknar tenant_id
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'boc_tickets' AND column_name = 'tenant_id') THEN
        ALTER TABLE boc_tickets ADD COLUMN tenant_id UUID REFERENCES boc_tenants(id);
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'boc_deals' AND column_name = 'tenant_id') THEN
        ALTER TABLE boc_deals ADD COLUMN tenant_id UUID REFERENCES boc_tenants(id);
    END IF;
    
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'boc_projects' AND column_name = 'tenant_id') THEN
        ALTER TABLE boc_projects ADD COLUMN tenant_id UUID REFERENCES boc_tenants(id);
    END IF;
END $$;

-- Uppdatera befintliga poster med default tenant
UPDATE boc_tickets SET tenant_id = '11111111-1111-1111-1111-111111111111' WHERE tenant_id IS NULL;
UPDATE boc_deals SET tenant_id = '11111111-1111-1111-1111-111111111111' WHERE tenant_id IS NULL;
UPDATE boc_projects SET tenant_id = '11111111-1111-1111-1111-111111111111' WHERE tenant_id IS NULL;

-- Skapa tenant-isolerade vyer
CREATE OR REPLACE VIEW v_tenant_summary AS
SELECT 
    t.id as tenant_id,
    t.name as tenant_name,
    t.slug,
    t.status,
    COUNT(DISTINCT u.id) as user_count,
    COUNT(DISTINCT c.id) as customer_count,
    COUNT(DISTINCT d.id) as deal_count,
    COUNT(DISTINCT e.id) as employee_count,
    COALESCE(SUM(d.value), 0) as total_pipeline
FROM boc_tenants t
LEFT JOIN boc_users u ON u.tenant_id = t.id
LEFT JOIN boc_customers c ON c.tenant_id = t.id
LEFT JOIN boc_deals d ON d.tenant_id = t.id AND d.status = 'open'
LEFT JOIN boc_employees e ON e.tenant_id = t.id
WHERE t.status = 'active'
GROUP BY t.id, t.name, t.slug, t.status;

-- Koncern-vy (hierarkisk)
CREATE OR REPLACE VIEW v_tenant_hierarchy AS
WITH RECURSIVE tenant_tree AS (
    -- Root tenants (koncerner)
    SELECT 
        id, name, slug, parent_id, 0 as level,
        id as root_id, name as root_name
    FROM boc_tenants
    WHERE parent_id IS NULL AND status = 'active'
    
    UNION ALL
    
    -- Child tenants (dotterbolag)
    SELECT 
        t.id, t.name, t.slug, t.parent_id, tt.level + 1,
        tt.root_id, tt.root_name
    FROM boc_tenants t
    JOIN tenant_tree tt ON t.parent_id = tt.id
    WHERE t.status = 'active'
)
SELECT * FROM tenant_tree;

-- Audit log för tenant-ändringar
CREATE TABLE IF NOT EXISTS boc_tenant_audit (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES boc_tenants(id),
    action VARCHAR(50) NOT NULL, -- created, updated, deleted, switched
    performed_by UUID REFERENCES boc_users(id),
    details JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_tenant_audit_tenant ON boc_tenant_audit(tenant_id);
CREATE INDEX IF NOT EXISTS idx_tenant_audit_created ON boc_tenant_audit(created_at);