-- ═══════════════════════════════════════════════
-- 1sixty8 POS — Employee Module Migration
-- 005: Super user flag, role_permissions, activity_log
-- ═══════════════════════════════════════════════

BEGIN;

-- ── Add super user flag to users ──
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_super BOOLEAN DEFAULT FALSE;
-- Super user flags are set by the provisioning service

-- ── Role Permissions (per-tenant, per-role access levels) ──
CREATE TABLE role_permissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL CHECK (role IN ('owner', 'manager', 'front_desk', 'technician')),
    module VARCHAR(50) NOT NULL,
    access_level VARCHAR(20) NOT NULL DEFAULT 'no_access'
        CHECK (access_level IN ('full', 'view_only', 'limited', 'no_access')),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(tenant_id, role, module)
);

CREATE INDEX idx_role_permissions_tenant_role ON role_permissions(tenant_id, role);

CREATE TRIGGER trg_role_permissions_updated_at BEFORE UPDATE ON role_permissions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── Activity Log ──
CREATE TABLE activity_log (
    id BIGSERIAL PRIMARY KEY,
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    action VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50),
    entity_id UUID,
    details JSONB DEFAULT '{}',
    ip_address VARCHAR(45),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_activity_log_tenant ON activity_log(tenant_id);
CREATE INDEX idx_activity_log_user ON activity_log(user_id);
CREATE INDEX idx_activity_log_action ON activity_log(action);
CREATE INDEX idx_activity_log_date ON activity_log(created_at DESC);

-- Default role permissions are seeded by the provisioning service with the actual tenant UUID

COMMIT;
