-- Migration 002: Add user role support
--
-- Single-tenant architecture: all tables retain tenant_id as extension point,
-- but the first deployed system operates in single-tenant mode.
--
-- This migration adds role-based access control to the users table.

------------------------------------------------------------------------
-- Extend users table with role field
------------------------------------------------------------------------

ALTER TABLE users ADD COLUMN role TEXT NOT NULL DEFAULT 'user'
    CHECK(role IN ('admin', 'user'));

-- Create index for role-based queries
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);

------------------------------------------------------------------------
-- Seed data: upgrade existing users to admin if they are the first user
------------------------------------------------------------------------

-- The first registered user (by created_at) becomes admin
-- This handles the case where users existed before this migration
UPDATE users
SET role = 'admin'
WHERE id = (SELECT id FROM users ORDER BY created_at ASC LIMIT 1);

------------------------------------------------------------------------
-- End of migration
------------------------------------------------------------------------