-- ============================================================
-- CashBook ERP - Multi-Tenancy & Multi-User Migration
-- Adds tenants, tenant_id to companies/users, user_businesses,
-- and updates seed data.
-- ============================================================

-- 1. Create tenants table
CREATE TABLE IF NOT EXISTS tenants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    domain VARCHAR(255),
    plan VARCHAR(50) DEFAULT 'free',           -- free, starter, professional, enterprise
    max_businesses INT DEFAULT 1,
    max_users INT DEFAULT 5,
    is_active BOOLEAN DEFAULT TRUE,
    settings JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. Add tenant_id to companies (businesses)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='companies' AND column_name='tenant_id') THEN
        ALTER TABLE companies ADD COLUMN tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE;
    END IF;
END $$;

-- 3. Add tenant_id to users
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='tenant_id') THEN
        ALTER TABLE users ADD COLUMN tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE;
    END IF;
END $$;

-- 4. Create user_businesses pivot table
CREATE TABLE IF NOT EXISTS user_businesses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    role VARCHAR(50) NOT NULL DEFAULT 'staff',   -- role within this specific business
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(user_id, company_id)
);

-- 5. Indexes
CREATE INDEX IF NOT EXISTS idx_tenants_slug ON tenants(slug);
CREATE INDEX IF NOT EXISTS idx_companies_tenant ON companies(tenant_id);
CREATE INDEX IF NOT EXISTS idx_users_tenant ON users(tenant_id);
CREATE INDEX IF NOT EXISTS idx_user_businesses_user ON user_businesses(user_id);
CREATE INDEX IF NOT EXISTS idx_user_businesses_company ON user_businesses(company_id);

-- 6. Migrate existing data: create a default tenant, assign existing companies & users
DO $$
DECLARE
    v_tenant_id UUID;
    rec RECORD;
BEGIN
    -- Only migrate if tenants table is empty (first run)
    IF NOT EXISTS (SELECT 1 FROM tenants LIMIT 1) THEN
        -- Create default tenant from the first company
        FOR rec IN SELECT id, name FROM companies ORDER BY created_at LIMIT 1
        LOOP
            INSERT INTO tenants (name, slug, plan, max_businesses, max_users)
            VALUES (rec.name, 'default', 'professional', 100, 100)
            RETURNING id INTO v_tenant_id;

            -- Assign all existing companies to this tenant
            UPDATE companies SET tenant_id = v_tenant_id WHERE tenant_id IS NULL;

            -- Assign all existing users to this tenant
            UPDATE users SET tenant_id = v_tenant_id WHERE tenant_id IS NULL;

            -- Auto-create user_businesses for every user → their current company
            INSERT INTO user_businesses (user_id, company_id, role, is_default)
            SELECT u.id, u.company_id, u.role, TRUE
            FROM users u
            WHERE NOT EXISTS (
                SELECT 1 FROM user_businesses ub WHERE ub.user_id = u.id AND ub.company_id = u.company_id
            );
        END LOOP;
    END IF;
END $$;

-- 7. Make tenant_id NOT NULL after migration (only if data exists)
-- We do this conditionally so it doesn't fail on empty databases
DO $$
BEGIN
    -- Check if there are any NULL tenant_ids remaining
    IF NOT EXISTS (SELECT 1 FROM companies WHERE tenant_id IS NULL) THEN
        -- Safe to add NOT NULL in future if desired
        NULL;
    END IF;
END $$;
