-- Migration 050: User-Company associations (multi-company support)
-- Allows one user to be associated with multiple companies
-- Maintains backward compatibility via trigger syncing users.company_id

BEGIN;

-- 1. Create user_companies table
CREATE TABLE IF NOT EXISTS user_companies (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL DEFAULT 'MANAGER',
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(user_id, company_id)
);

-- 2. Indexes
CREATE INDEX IF NOT EXISTS idx_user_companies_user_id ON user_companies(user_id);
CREATE INDEX IF NOT EXISTS idx_user_companies_company_id ON user_companies(company_id);
-- No partial unique index on is_primary - enforced by BEFORE trigger instead
-- (AFTER trigger can't clear old primary before unique constraint check)

-- 3. Migrate existing data from users table
INSERT INTO user_companies (user_id, company_id, role, is_primary, created_at)
SELECT id, company_id,
       COALESCE(NULLIF(company_role, 'NONE'), 'MANAGER'),
       TRUE,
       COALESCE(created_at, NOW())
FROM users
WHERE company_id IS NOT NULL
ON CONFLICT (user_id, company_id) DO NOTHING;

-- 4. BEFORE trigger: enforce single primary + clear others
CREATE OR REPLACE FUNCTION sync_user_primary_company()
RETURNS TRIGGER AS $$
BEGIN
    -- On INSERT or UPDATE with is_primary = TRUE
    IF TG_OP IN ('INSERT', 'UPDATE') AND NEW.is_primary = TRUE THEN
        -- Clear other primary flags for this user BEFORE the row is written
        UPDATE user_companies
        SET is_primary = FALSE, updated_at = NOW()
        WHERE user_id = NEW.user_id
          AND id != NEW.id
          AND is_primary = TRUE;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_sync_user_primary_company ON user_companies;
CREATE TRIGGER trg_sync_user_primary_company
    BEFORE INSERT OR UPDATE ON user_companies
    FOR EACH ROW
    EXECUTE FUNCTION sync_user_primary_company();

-- 5. AFTER trigger: sync to users table
CREATE OR REPLACE FUNCTION sync_user_primary_to_users()
RETURNS TRIGGER AS $$
BEGIN
    -- On INSERT or UPDATE with is_primary = TRUE -> sync to users
    IF TG_OP IN ('INSERT', 'UPDATE') AND NEW.is_primary = TRUE THEN
        UPDATE users
        SET company_id = NEW.company_id,
            company_role = NEW.role
        WHERE id = NEW.user_id;
    END IF;

    -- On DELETE of primary association
    IF TG_OP = 'DELETE' AND OLD.is_primary = TRUE THEN
        -- Try to promote another association to primary
        UPDATE user_companies
        SET is_primary = TRUE, updated_at = NOW()
        WHERE id = (
            SELECT id FROM user_companies
            WHERE user_id = OLD.user_id AND id != OLD.id
            ORDER BY created_at ASC
            LIMIT 1
        );

        -- If no other association exists, clear users table
        IF NOT FOUND THEN
            UPDATE users
            SET company_id = NULL, company_role = 'NONE'
            WHERE id = OLD.user_id;
        END IF;
    END IF;

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_sync_user_primary_to_users ON user_companies;
CREATE TRIGGER trg_sync_user_primary_to_users
    AFTER INSERT OR UPDATE OR DELETE ON user_companies
    FOR EACH ROW
    EXECUTE FUNCTION sync_user_primary_to_users();

-- 6. Permissions
GRANT ALL ON TABLE user_companies TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE user_companies_id_seq TO nordabiz_app;

COMMIT;