-- Team & Permissions System (RBAC)
-- Manage user roles and granular permissions

-- Roles table
CREATE TABLE IF NOT EXISTS roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  description TEXT,
  permissions JSONB NOT NULL DEFAULT '{}', -- { "leads.create": true, "leads.delete": false, ... }
  is_system BOOLEAN DEFAULT false, -- System roles can't be deleted
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- User roles junction table
CREATE TABLE IF NOT EXISTS user_roles (
  user_id UUID NOT NULL,
  role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  assigned_by UUID,
  assigned_at TIMESTAMPTZ DEFAULT NOW(),
  PRIMARY KEY (user_id, role_id)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_user_roles_user ON user_roles(user_id);
CREATE INDEX IF NOT EXISTS idx_user_roles_role ON user_roles(role_id);
CREATE INDEX IF NOT EXISTS idx_roles_name ON roles(name);

-- Extend user_xp with permissions column for caching
ALTER TABLE user_xp ADD COLUMN IF NOT EXISTS permissions JSONB DEFAULT '{}';

-- Function to get merged permissions for a user
CREATE OR REPLACE FUNCTION get_user_permissions(p_user_id UUID)
RETURNS JSONB AS $$
DECLARE
  result JSONB := '{}';
  role_perms JSONB;
BEGIN
  -- Get all role permissions for this user
  FOR role_perms IN
    SELECT r.permissions
    FROM user_roles ur
    JOIN roles r ON r.id = ur.role_id
    WHERE ur.user_id = p_user_id
  LOOP
    result := result || role_perms;
  END LOOP;

  RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Trigger to update cached permissions when roles change
CREATE OR REPLACE FUNCTION update_user_cached_permissions()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
    UPDATE user_xp
    SET permissions = get_user_permissions(NEW.user_id),
        updated_at = NOW()
    WHERE user_id = NEW.user_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE user_xp
    SET permissions = get_user_permissions(OLD.user_id),
        updated_at = NOW()
    WHERE user_id = OLD.user_id;
    RETURN OLD;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_update_cached_permissions ON user_roles;
CREATE TRIGGER trigger_update_cached_permissions
  AFTER INSERT OR UPDATE OR DELETE ON user_roles
  FOR EACH ROW
  EXECUTE FUNCTION update_user_cached_permissions();

-- RLS policies
ALTER TABLE roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_roles ENABLE ROW LEVEL SECURITY;

CREATE POLICY "roles_authenticated_read"
  ON roles FOR SELECT TO authenticated
  USING (true);

CREATE POLICY "roles_service_all"
  ON roles FOR ALL TO service_role
  USING (true) WITH CHECK (true);

CREATE POLICY "user_roles_authenticated_read"
  ON user_roles FOR SELECT TO authenticated
  USING (true);

CREATE POLICY "user_roles_service_all"
  ON user_roles FOR ALL TO service_role
  USING (true) WITH CHECK (true);

-- Seed default system roles
INSERT INTO roles (name, description, permissions, is_system) VALUES
  ('admin', 'Full system access - can manage everything', '{
    "*": true
  }', true),

  ('manager', 'Team management + all caller permissions', '{
    "team.view": true,
    "team.manage": true,
    "reports.view": true,
    "reports.export": true,
    "challenges.create": true,
    "leads.view": true,
    "leads.create": true,
    "leads.update": true,
    "leads.delete": true,
    "leads.assign": true,
    "leads.import": true,
    "leads.export": true,
    "calls.view": true,
    "calls.make": true,
    "calls.log": true,
    "sms.view": true,
    "sms.send": true,
    "sms.bulk": true,
    "sequences.view": true,
    "sequences.manage": true,
    "playbook.view": true,
    "playbook.manage": true,
    "settings.view": true
  }', true),

  ('closer', 'Senior sales rep with additional permissions', '{
    "team.view": true,
    "reports.view": true,
    "leads.view": true,
    "leads.create": true,
    "leads.update": true,
    "leads.assign": true,
    "calls.view": true,
    "calls.make": true,
    "calls.log": true,
    "sms.view": true,
    "sms.send": true,
    "sms.bulk": true,
    "sequences.view": true,
    "sequences.enroll": true,
    "playbook.view": true,
    "settings.view": true
  }', true),

  ('caller', 'Standard sales rep - basic operations', '{
    "leads.view": true,
    "leads.create": true,
    "leads.update": true,
    "calls.view": true,
    "calls.make": true,
    "calls.log": true,
    "sms.view": true,
    "sms.send": true,
    "sequences.view": true,
    "sequences.enroll": true,
    "playbook.view": true
  }', true),

  ('viewer', 'Read-only access for observers', '{
    "leads.view": true,
    "calls.view": true,
    "sms.view": true,
    "reports.view": true,
    "sequences.view": true,
    "playbook.view": true
  }', true)
ON CONFLICT (name) DO UPDATE SET
  permissions = EXCLUDED.permissions,
  description = EXCLUDED.description;

-- Assign default roles to existing users based on their current role
INSERT INTO user_roles (user_id, role_id)
SELECT ux.user_id, r.id
FROM user_xp ux
JOIN roles r ON r.name = COALESCE(ux.role, 'caller')
WHERE ux.is_active = true
ON CONFLICT (user_id, role_id) DO NOTHING;
