-- Fix auth tables for erp_construccion_demo

-- 1. Add missing columns to auth.users
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS roles VARCHAR[] DEFAULT ARRAY['viewer']::VARCHAR[];
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS default_tenant_id UUID;
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMPTZ;
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS username VARCHAR(100);

-- 2. Add missing columns to auth.tenants
ALTER TABLE auth.tenants ADD COLUMN IF NOT EXISTS code VARCHAR(50) UNIQUE;
ALTER TABLE auth.tenants ADD COLUMN IF NOT EXISTS settings JSONB DEFAULT '{}';
ALTER TABLE auth.tenants ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMPTZ;

-- Update tenants code from slug
UPDATE auth.tenants SET code = slug WHERE code IS NULL;

-- 3. Create refresh_tokens table
CREATE TABLE IF NOT EXISTS auth.refresh_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    token TEXT NOT NULL UNIQUE,
    expires_at TIMESTAMPTZ NOT NULL,
    revoked_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    user_agent VARCHAR(500),
    ip_address VARCHAR(45)
);

CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user ON auth.refresh_tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_token ON auth.refresh_tokens(token);
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_expires ON auth.refresh_tokens(expires_at);

-- 4. Create test tenant if not exists
INSERT INTO auth.tenants (id, name, slug, code, is_active, created_at, updated_at)
VALUES (
    'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
    'Test Company',
    'test-company',
    'test-company',
    true,
    NOW(),
    NOW()
) ON CONFLICT (id) DO NOTHING;

-- 5. Create admin user with password 'Admin123!'
INSERT INTO auth.users (
    id, tenant_id, email, password_hash, first_name, last_name,
    is_active, email_verified, roles, default_tenant_id, created_at, updated_at
)
VALUES (
    'b0eebc99-9c0b-4ef8-bb6d-6bb9bd380a22',
    'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
    'admin@test.com',
    '$2a$10$4M7jT3YmH14xyx3L7uojmuRFyggZcM/qb8A/54B2XXDtYBmVI7/J2',
    'Admin',
    'User',
    true,
    true,
    ARRAY['admin']::VARCHAR[],
    'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
    NOW(),
    NOW()
) ON CONFLICT (id) DO UPDATE SET
    password_hash = '$2a$10$4M7jT3YmH14xyx3L7uojmuRFyggZcM/qb8A/54B2XXDtYBmVI7/J2',
    roles = ARRAY['admin']::VARCHAR[],
    default_tenant_id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';

-- 6. Verify
SELECT 'Tenant created:', id, name FROM auth.tenants WHERE id = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11';
SELECT 'User created:', id, email, roles FROM auth.users WHERE email = 'admin@test.com';